Automatically Populate a result from each cell in Column "B" using vlookup formula-CodePudding

I am new in VBA, please help me to create a code based on the lookup value in column "B" and it will generate automatically a result in Column C, and Column H (Please see screenshot below).

Please help me correct my code (below):

    Private Sub CommandButton4_Click()
       If Sheet1.Range("B13") = "" Is Nothing Then Application.WorksheetFunction.VLookup(Sheet1.Range("B13"),Sheet4.Range("''''A4:D120000"),3,0)
     End If
     End Sub

CodePudding user response：

Maybe something like this to get you on the right track.

You don't say where to print the result. I also think the index match solution is faster (array would be best), but this should work but can take some time to execute.

Code:

Private Sub CommandButton4_Click()

Dim i As Long
Dim lrow As Long
Dim Val_find As String

lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row 'Find last row in Sheet1

For i = 7 To lrow
    If Sheet1.Cells(i, "B") = "" Then
        Val_find = Application.WorksheetFunction.VLookup(Sheet1.Cells(i, "B"), Sheet4.Range("A4:D120000"), 3, 0) 'Original formula
        'Val_find = Application.WorksheetFunction.Index(Sheet4.Range("C4:C120000"), WorksheetFunction.Match(Sheet1.Cells(i, "B"), Sheet4.Range("A4:A120000"), 0)) 'should be faster
        Sheet1.Cells(j, "C").Value = Val_find 'Print value to worksheet
    End If
Next i

End Sub

CodePudding user response：

Write the formula to the cell rather than the value.

Private Sub CommandButton4_Click()
   
    Const COL_DESCR = 3 ' lookup
    Const COL_PRICE = 4
    Dim descr As String, price As String, tbl As String, i As Long
    tbl = "'" & Sheet4.Name & "'!R1C1:R120000C4"
    
    descr = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1]," & tbl & "," & COL_DESCR & ", 0))"
    price = "=IF(RC[-6]="""","""",VLOOKUP(RC[-6]," & tbl & "," & COL_PRICE & ", 0))"
    
    i = 13
    With Sheet1
        .Range("C" & i).FormulaR1C1 = descr
        .Range("H" & i).FormulaR1C1 = price
    End With
    
End Sub