Excel 2010 VBA Array: Replace Values in a Column Of Cells

[ajay_gajree]

Sub CountryCorrection()
    Dim rCell As Range
    Dim rRng As Range
    Dim dNumbers As Double
    Dim strCountries As String
    Dim lMatch As Long
    dNumbers = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", _
                    "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", _
                    "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", _
                    "48", "49")
    
    strCountries = Array("Argentina", "Australia", "Botswana", "Brazil", "Canada", "China", "Egypt", _
                    "France", "Germany", "Ghana", "Gibraltar", "Hong Kong", "India", "Indonesia", _
                    "Ireland", "Israel", "Italy", "Japan", "Kenya", "Lithuania", "Malaysia", _
                    "Mauritius", "Mexico", "Monaco", "Mozambique", "Namibia", "Oman", "Pakistan", _
                    "Philippines", "Portugal", "Qatar", "Russia", "Saudi Arabia", "Seychelles", _
                    "Singapore", "South Africa", "South Korea", "Spain", "Switzerland", "Taiwan", _
                    "Tanzania", "Thailand", "UAE", "Uganda", "Ukraine", "United Kingdom", "USA", _
                    "Zambia", "Zimbabwe")
    With Sheet1
        Set rRng = ThisWorkbook.Worksheets("Deletions").Range("F1", .Cells(.Rows.Count, 3).End(xlUp))
    End With
    For Each rCell In rRng.Cells
        lMatch = Application.WorksheetFunction.Match(rCell.Value, dNumbers, False)
        rCell.Value = strCountries(lMatch)
        '/rCell.Offset(0, 1).Value = vaGroups(lMatch - 1)
    Next rCell
 
End Sub

Hi,

I am trying to change numerical values in a Table in my worksheets (Column F) using two arrays as above.

When I run the above I am getting the compile error - "Expected Array"

Can anyone with a fresh pair of eyes see what I have done wrong? Also confirm if what I have done will actually work!

Thanks a lot

 

[ajay_gajree]

I am now trying the below, again without success.

I am loading numbers 1-49 from a range in a worksheet in my workbook into CountryNumberArray()
And then loading numbers a list of Corresponding Countries from an adjacent range in a worksheet in my workbook into CountryArray()

I then am trying to loop through a column on a different worksheet in my workbook and match each number with a number in CountryNumberArray() and replace with the corresponding Country value in CountryArray()

Any help would be appreciated! Error message I get is "Unable to get the Match property of the WorksheetFunction Class.

Sub UpdateRange()
Dim rCell                   As Range
Dim rRng                    As Range
Dim lMatch                  As Long
Dim CountryNumberArray()
Dim CountryArray()

With ThisWorkbook.Worksheets("Sheet1").Range("A2:A50")
    ReDim CountryNumberArray(1 To .Rows.Count)
    '/CountryNumberArray = ThisWorkbook.Worksheets("Sheet1").Range("A2:A50")
End With

With ThisWorkbook.Worksheets("Sheet1").Range("C2:C50")
    ReDim CountryArray(1 To .Rows.Count)
    CountryArray = ThisWorkbook.Worksheets("Sheet1").Range("C2:C50")
End With

Set rRng = ThisWorkbook.Worksheets("Deletions").Range("F1:F500")

For Each rCell In rRng.Cells
    lMatch = Application.WorksheetFunction.Match(rCell.Value, CountryNumberArray, False)
    rCell.Value = CountryArray(lMatch - 1)
Next rCell

End Sub

 

[Andrew Poulsom]

In your original code you need to declare dNumbers and strCountries As Variant.

 

[ajay_gajree]

Thanks a lot, that has got rid of the first error.

Now both sets of code come up with this error
"Unable to get the Match property of the WorksheetFunction Class"

So I have this line wrong, can anyone help me with how this is written incorrectly at all?

lMatch = Application.WorksheetFunction.Match(rCell.Value, CountryNumberArray, False)

 

[ajay_gajree]

I was starting my range with the heading row, which not a value in my range, so ignore me!