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.

1) You can use Application.Match or Find method to find the correct column and then reference that in your code. 2) There are countless examples online showing how to find the last row/column. Have you really tried and not been able to apply anything? — SJR, Oct 19 '17 at 08:16
Wouldn't it be best to format it as a table and to use table intrinsic properties? — Vincent G, Oct 19 '17 at 09:23
Hi @SJR. I pasted my current solution, if that helps with answering. — Bradi, Oct 19 '17 at 09:24
Looks good. Remove the `Sheets("Sheet1").` from the statement inside the If because Left is not a property of the worksheet. You can also remove the Selects to make your code more efficient. — SJR, Oct 19 '17 at 09:35

score 0 · Answer 1 · answered Oct 19 '17 at 09:42

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

Thank you very much for the answer and the comments as well. Didn't know that With I can avoid repeating the same word. :) — Bradi, Oct 19 '17 at 12:02

Populating values with IF condition and headers

1 Answers1