+ Reply to Thread
Results 1 to 2 of 2

Error with match in VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    03-07-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Error with match in VBA

    Hello. I am receiving an error (Run-time Error 1004) when I run the code that follows. I think it is first having an issue with the Match line. Also, I do not like using R1C1 at the bottom, but I recorded a macro initially to figure out how to do that, and that is what showed up. Thanks for any help.


    Sub Titles()
            
        Dim sCountry As String
        Dim iCountryCount As Integer
        Dim rCountries As Range
        Dim iStartList As Integer
        Dim iTotalCities As Integer
        Dim iNumCountries As Integer
        
        Set rCountries = ThisWorkbook.Names("CountryList").RefersToRange
        iNumCountries = rCountries.Cells.Count
        
        For iCountryCount = 1 To iNumCountries
       
            sCountry = Worksheets("CountryData").Cells(iCountryCount + 1, 1).Value
            iStartList = WorksheetFunction.Match(sCountry, Sheets("CityMapping").Range("A1:A1000"), 0)
            iTotalCities = WorksheetFunction.CountIf(Sheets("CityMapping").Range("A1:A1000"), sCountry)
                    
                    
            sCountry = Replace(sCountry, " ", "")
                   
            ThisWorkbook.Names.Add Name:=(sCountry & "Cities"), RefersToR1C1:= _
            "=CityMapping!R[-iStartList+8]C[-9]:R[-iTotalCities + 8 + iStartList]C[-9]"
            
        
        Next iCountryCount
        
            
    End Sub

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,920

    Re: Error with match in VBA

    Post a sample of your workbook.
    When you are in Advanced reply, click on the paperclip to open the upload window.

    View Pic
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1