How to vlookup in a column in a Range of an other column's length using VBA?

Question

I want to vlookup values in a column, but only in a range of an other columns length. (By an other columns length I mean how many cells it has with values). The A column has IDs, and the F column has product codes. In the I column I want to write each and every product codes name. (Im doing my vlookup from an other sheet for products). If the vlookup fails, instead of #N/A I am writing ERROR in the specific row. It can happen that the user enters an ID in the A column, but forget to enter the product code, so I want to vlookup the F column until the number of rows (entered IDs) in the A column (so the user can see an error because of the existing ID with no product code) How should I overwrite the below code? I know that I should overwrite this line somehow: For Each Acell In Sheet1.range(ToLookup, range(columnName & Rows.Count).End(xlUp))

Sub Validation()
    Dim inputTemplate As Worksheet
    Set inputTemplate = Worksheets("Input template")
    Dim accounts As Worksheet
    Set accounts = Worksheets("Accounts")
    Dim products As Worksheet
    Set products = Worksheets("Products")

    Call Clear(inputTemplate)
    Call aValidation(inputTemplate, accounts, inputTemplate.range("F2"), accounts.range("A1:B45"), "F", 4, -5)
    Call aValidation(inputTemplate, products, inputTemplate.range("E2"), products.range("A1:C33"), "E", 4, -4)

End Sub

Sub aValidation(Sheet1 As Worksheet, Sheet2 As Worksheet, ToLookup As range, LookupTable As range, columnName As String, LookupPos As Integer, CIDPos As Integer)
    Dim Acell As range

    For Each Acell In Sheet1.range(ToLookup, range(columnName & Rows.Count).End(xlUp))

        Acell.Offset(0, LookupPos).HorizontalAlignment = xlLeft
        Acell.Offset(0, LookupPos).Formula = Application.VLookup(Acell, LookupTable, 2, False)
        Debug.Print (Acell.Offset(0, -4).Value)


        If Application.WorksheetFunction.IsNA(Acell.Offset(0, LookupPos).Value) And IsEmpty(Acell.Offset(0, CIDPos)) = False Then
            Acell.Offset(0, LookupPos).Interior.Color = RGB(255, 0, 0)
            Acell.Offset(0, LookupPos).Value = "ERROR"
            Acell.Offset(0, LookupPos).HorizontalAlignment = xlVAlignCenter
        End If
    Next Acell

End Sub

Sub Clear(Sheet1 As Worksheet)
    Sheet1.Columns("H:J").Rows("2:" & Rows.Count).ClearContents
    Sheet1.Columns("H:J").Rows("2:" & Rows.Count).ClearFormats
End Sub

Looz Looz · Accepted Answer · 2019-07-26T15:30:38

In the meantime I got the answer:

For Each Acell In Sheet1.range(ToLookup, range(columnName & Sheet1.Columns("A").Cells.SpecialCells(xlCellTypeConstants).Count))

How to vlookup in a column in a Range of an other column's length using VBA?

1 Answers