Populating values with IF condition and headers

Question

So here's my situation. See the picture for better understanding. Example table

So what I'm trying to do is that, IF cell in columnn C isn't empty, the excel will populate first 3 letters of Column B to columnn D. I used following macro to do so:

Sub FillCountryCode()

Sheets("Sheet1").Range("D2:D20").Formula = "=IF(C2 <> """", LEFT(B2,3), """")"

End Sub

So I've 2 problems with this solution.

1st) If I move the Country Code column, let's say to column F, the macro won't work anymore as it doesn't realize the column has been moved. So what's the easiest way to change macro to work so that it searches the right columns according to header name (for example Country Code) and then goes trough all the rows (in my actual excel file there are hundreds of rows, when example table only has 8). So it doesn't actually matter, in which column the relevant headers and cells for macro are located.

2nd) At the moment I'm manually determining the range for macro, what's the correct command to make macro check all the rows in file. (All the rows have values in Car Brand and Country)

Here's the solution I came up, when trying to solve this.

Sub FillCountryCode()


Worksheets("Sheet1").Activate
Rows(3).Select

Set CountRY = Selection.Find(What:="COUNTRY", After:=ActiveCell, LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)

Set ENGINE = Selection.Find(What:="ENGINE", After:=ActiveCell, LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)

Set COUNTRYCODE = Selection.Find(What:="COUNTRYCODE", After:=ActiveCell, LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)

Dim LastItemRow As Long

LastItemRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

For pointer = 4 To LastItemRow

    If Cells(pointer, ENGINE.Column) <> "" Then

        Cells(pointer, COUNTRYCODE.Column).Value = Sheets("Sheet1").Left(Cells(pointer, COUNTRY.Column), 3)

    End If

Next pointer

End Sub

When trying to run this solution, there's some problem in the IF-condition, but I don't understand what is it. Can some one help me? THe error I get is: Error 438: Object doesn't support this property or method.

Answer 1

I think you deserve the points as you have done most of the work. I'm just posting your code with a few amendments to make it slightly more efficient, and avoid error messages popping up. I've added a few comments to explain the changes

Sub FillCountryCode()

Dim COUNTRY As Range, ENGINE As Range, COUNTRYCODE As Range 'declared all your variables
Dim LastItemRow As Long, pointer As Long

With Worksheets("Sheet1")   'removes Selects and need to repeat sheet reference elsewhere
    Set COUNTRY = .Rows(3).Find(What:="COUNTRY", LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)   'remove ActiveCell reference as will error if not in search range
    Set ENGINE = .Rows(3).Find(What:="ENGINE", LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
    Set COUNTRYCODE = .Rows(3).Find(What:="COUNTRYCODE", LookIn:=xlFormulas, _
                           LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)

    If Not COUNTRY Is Nothing And Not COUNTRYCODE Is Nothing And Not ENGINE Is Nothing Then  'avoids error if text not found
        LastItemRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For pointer = 4 To LastItemRow
            If .Cells(pointer, ENGINE.Column) <> "" Then
                .Cells(pointer, COUNTRYCODE.Column).Value = Left(.Cells(pointer, COUNTRY.Column), 3)
            End If
        Next pointer
    End If
End With

End Sub