我是 VBA 新手，請幫助我根據“B”列中的查找值創建一個代碼，它將在 C 列和 H 列中自動生成結果（請參見下面的螢屏截圖）。
請幫我更正我的代碼（如下）：
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
uj5u.com熱心網友回復：
將公式寫入單元格而不是值。
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
或使用的值 Application.Match
Private Sub CommandButton4_Click()
Dim r, i As Long, rngLU As Range
Dim price, descr As String
i = 13
Set rngLU = Sheet4.Range("A4:A120000")
With Sheet1
If .Range("B" & i).Value2 <> "" Then
r = Application.Match(.Range("B" & i).Value2, rngLU, 0)
If IsError(r) Then
descr = "#N/A"
price = "#N/A"
Else
descr = rngLU.Cells(r, 3) ' col C
price = rngLU.Cells(r, 4) ' col D
End If
End If
.Cells(i, "C") = descr
.Cells(i, "H") = price
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/391131.html