vba - 在 VA 的 IF 内循环 VLOOKUP

Question

我是 VBA 新手，刚开始学习循环。

这是我的代码:

sub worksheet_change(byval target as range)

application.screenupdating = false
application.enableevents = false

dim book1 as workbooks
dim customer as range, rang as range, jdiskon as range, pelanggan as range, lookharga as range, diskon as range
dim rout(1 to 10) as variant, i as long
set book1 = workbooks("database.xlsx")
set rang = book1.sheets("DB").range("A6:N84")
set look harga book1.sheets("harga").range("B4:E50")

set pelanggan = range("E7")
set alamat = range("E8")
set jdiskon = range("M26")
set diskon = range("P3")

getalamat = application.worksheetfunction.vlookup(pelanggan, rang, 13, false)
jenisdiskon = application.worksheetfunction.vlookup(pelanggan, rang, 10, false)
getdiskon = application.worksheetfunction.vlookup(pelanggan, rang, 8, false)
getharga = application.worksheetfunction.vlookup(range("D13") & range("E13"), lookharga, 4, false)
getharga1 = application.worksheetfunction.vlookup(range("D14") & range("E14"), lookharga, 4, false)
getharga2 = application.worksheetfunction.vlookup(range("D15") & range("E15"), lookharga, 4, false)
getharga3 = application.worksheetfunction.vlookup(range("D16") & range("E16"), lookharga, 4, false)
getharga4 = application.worksheetfunction.vlookup(range("D17") & range("E17"), lookharga, 4, false)

alamat.value = getalamat
jdiskon.value = jenisdiskon
diskon.value = getdiskon / 100

if jdiskon = "nett" then
    range("M13").value = getharga - (getharga * diskon)
    range("M14").value = getharga1 - (getharga1 * diskon)
    range("M15").value = getharga2 - (getharga2 * diskon)
    range("M16").value = getharga3 - (getharga3 * diskon)
    range("M17").value = getharga4 - (getharga4 * diskon)
elseif jdiskon.value = "pot" then
    range("M13").value = getharga
    range("M14").value = getharga1
    range("M15").value = getharga2
    range("M16").value = getharga3
    range("M17").value = getharga4
    range("L25").value = diskon
end if

application.enableevents = true

end sub

现在我只是通过复制/粘贴来使用手动代码。但是，我想通过使用循环来简化 IF 中的代码，因为它似乎更有效。

最好的方法是什么？

Answer 1

编写如下代码:

Dim book1 As Workbooks
Dim customer As Range, rang As Range, jdiskon As Range, pelanggan As Range, lookharga As Range, diskon As Range
Dim rout(1 To 10) As Variant, i As Long
Set book1 = Workbooks("database.xlsx")
Set rang = book1.Sheets("DB").Range("A6:N84")
Set lookharga = book1.Sheets("harga").Range("B4:E50")

Set pelanggan = Range("E7")
Set alamat = Range("E8")
Set jdiskon = Range("M26")
Set diskon = Range("P3")

getalamat = Application.WorksheetFunction.VLookup(pelanggan, rang, 13, False)
jenisdiskon = Application.WorksheetFunction.VLookup(pelanggan, rang, 10, False)
getdiskon = Application.WorksheetFunction.VLookup(pelanggan, rang, 8, False)
'New code with if statement starts from here
alamat.Value = getalamat
jdiskon.Value = jenisdiskon
diskon.Value = getdiskon / 100

For i = 13 To 17

getharga = Application.WorksheetFunction.VLookup(Range("D" & i) & Range("E" & i), lookharga, 4, False)

If jdiskon = "nett" Then
    Range("M" & i).Value = getharga - (getharga * diskon)

ElseIf jdiskon.Value = "pot" Then
    Range("M" & i).Value = getharga
    Range("L25").Value = diskon
End If
' new code with if statement ends here
Next
Application.EnableEvents = True

End Sub