VBA - Need formula to work on dynamic column - But getting a variable error

[TheRedCardinal]

Hi all,

I have a simple Sub that replaces one value in a cell with another from a lookup table. It loops around the length of the column replacing row by row

Sub UpdateCountry()

Dim lr As Long, i As Long
Dim CountryCol As Range
Dim x As Variant


    Set ws1 = Sheets("2. Final Data")
    ws1.Select
    
    lr = Range("A1").End(xlDown).Row
    
    With ws1
    
        CountryCol = .Range("A1:Z1").Find("Country/region")
    
    End With
    
    For i = 1 To lr
        x = Application.VLookup(Cells(i, CountryCol), Sheets("Data Sheet").Columns("A:B"), 2, False)
        If Not (IsError(x)) Then
            Cells(i, CountryCol).Value = x
        End If
    
    Next i


End Sub

I get an "Object Variable or With Block variable not set" error highlighting the line between With WS1 and End With.

CountryCol Variable is clearly declared within the Sub; WS1 is declared as a worksheet in the Public section of the module.

The text it is looking for is definitely there too.

I have missed something obvious - so can anybody spot it?

Thanks!

 

[Fluff]

As the variable is an object (in this case a range) when you assign a value to it you need to precede it with Set like

Set CountryCol = .Range("A1:Z1").Find("Country/region")

 

[TheRedCardinal]

Of course I do!

I knew it was something stupid.

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback