calculating loop didn't work vba

Question

I am trying to calculate from range M13:M22 using some conditional values and looping, but some of my code just works only in cell M13 and doesn't loop to cell M22. How do I solve this problem?

Here is my code:

Private Sub CommandButton1_Click()

    Dim pelanggan As Range, alamat As Range, diskon As Range, jdiskon As Range, tanggal As Range, jtempo As Range
    Dim rout(1 To 10) As Variant, i As Long
    Dim path As String

    path = "\\Faizal\Data D Faizal\Daftar Harga\Price List"
    Filename = Dir(path & "database.xlsx")

    Set pelanggan = Range("E7")
    Set alamat = Range("E8")
    Set diskon = Range("L25")
    Set tanggal = Range("L7")
    Set jdiskon = Range("P13")
    Set jtempo = Range("K30")

    getalamat = Application.WorksheetFunction.VLookup(pelanggan & Range("J7"), Workbooks("database.xlsx").Worksheets("DB").Range("A6:N1350"), 14, False)
    getdiskon = Application.WorksheetFunction.VLookup(pelanggan & Range("J7"), Workbooks("database.xlsx").Worksheets("DB").Range("A6:N1350"), 6, False)
    getjdiskon = Application.WorksheetFunction.VLookup(pelanggan & Range("J7"), Workbooks("database.xlsx").Worksheets("DB").Range("A6:N1350"), 11, False)
    getjtempo = Application.WorksheetFunction.VLookup(pelanggan & Range("J7"), Workbooks("database.xlsx").Worksheets("DB").Range("A6:N1350"), 13, False)

    alamat.Value = getalamat
    diskon.Value = getdiskon / 100
    jdiskon.Value = getjdiskon
    tanggal.Value = DateValue(Now)
    jtempo.Value = getjtempo
    'here is the calculation that won't go loop
    For i = 13 To 22
        getharga = Application.WorksheetFunction.VLookup(Range("D" & i) & Range("E" & i), Workbooks("database.xlsx").Worksheets("Gold").Range("E4:H80"), 4, False)
        If jdiskon = "Nett" Then
            Range("M" & i).Value = getharga - (getharga * diskon)
            Range("L25").ClearContents
        ElseIf jdiskon = "Pot" Then
            Range("M" & i).Value = getharga
            Range("L25").Value = diskon
        ElseIf jdiskon = "Diskon Kitir" Then
            Range("M" & i).Value = getharga
            Range("L25").ClearContents
        End If

    Next

End Sub

Can you describe the actual problem please? Can you also be clear on what code is working and what isn't? — Enigmativity, Jun 01 '15 at 02:22
here is the explanation. i want to get price value according to the discount in my DB. If i get "pot" value from my discount then it will only show the actual value from the price list from range M13 to M22, but if i get "nett" value it will calculate the value from price list - discount and place in cell M13 to M22 and that is the one that didn't work. The "nett calculating" only work in cell M12 and didn't loop till cell M22. Thanks — uniks, Jun 01 '15 at 02:58
Your loop looks fine. You get the value for jdiskon once and then go into the loop. Maybe you need a final `Else` clause in your `If jdiskon = ... End If` to check if you get a different value. Maybe simply write the value of jdiskon to `Range("M" & i)` in the Else. Also be wary of case-sensitivity, are you getting "Nett" or "nett" or "NETT" for jdiskon? — ChipsLetten, Jun 01 '15 at 10:06
@ChipsLetten : i have tried your tips to change the last elseif into else but it still didn't work — uniks, Jun 04 '15 at 04:06
Can you edit your post to show the changed code? What about the other points in my comment - case sensitivity? What did you get in the sheet when you wrote jdiskon to it? — ChipsLetten, Jun 04 '15 at 08:33
Did you find anything posted useful? Please post feedback, vote and/or accept according to what you found. — sancho.s ReinstateMonicaCellio, Jul 05 '15 at 10:19

score 0 · Answer 1 · edited May 23 '17 at 12:14

Your question: "... my code just works only in cell M13 and doesn't loop to cell M22. How do I solve this problem?"

Your loop is ok, so that is not the problem you have to solve. You have to debug to find the causes of your loop not performing the actions you mean to.

I am posting below modified code, with two features: 1) it fully qualifies Ranges, so you avoid unexpected errors, you may want to check this; 2) it uses MsgBoxes, one way of debugging.

This will likely pinpoint the "error".

Private Sub CommandButton1_Click()

    Dim pelanggan As Range, alamat As Range, diskon As Range, jdiskon As Range, tanggal As Range, jtempo As Range
    Dim rout(1 To 10) As Variant, i As Long
    Dim path As String

    path = "\\Faizal\Data D Faizal\Daftar Harga\Price List"
    Filename = Dir(path & "database.xlsx")

    Dim wb as Workbook, ws1 as Worksheet, ws2 as Worksheet, rng1 as Range
    Set wb = Workbooks("database.xlsx")
    Set ws1 = wb.Worksheets("DB")
    Set ws2 = wb.Worksheets("Gold")
    Set rng1 = ws.Range("A6:N1350")

    Set pelanggan = ws1.Range("E7")
    Set alamat = ws1.Range("E8")
    Set diskon = ws1.Range("L25")
    Set tanggal = ws1.Range("L7")
    Set jdiskon = ws1.Range("P13")
    Set jtempo = ws1.Range("K30")

    Dim rng2 as Range
    Set rng2 = ws1.Range(pelanggan.Value & ws1.Range("J7").Value)

    getalamat = Application.WorksheetFunction.VLookup(rng2, rng1, 14, False)
    getdiskon = Application.WorksheetFunction.VLookup(rng2, rng1, 6, False)
    getjdiskon = Application.WorksheetFunction.VLookup(rng2, rng1, 11, False)
    getjtempo = Application.WorksheetFunction.VLookup(rng2, rng1, 13, False)

    alamat.Value = getalamat
    diskon.Value = getdiskon / 100
    jdiskon.Value = getjdiskon
    tanggal.Value = DateValue(Now)
    jtempo.Value = getjtempo
    'here is the calculation that won't go loop
    For i = 13 To 22
        Dim rng3 as Range
        Set rng3 = ws1.Range(ws1.Range("D" & i).Value & ws1.Range("E" & i).Value)
        getharga = Application.WorksheetFunction.VLookup(rng3, ws2.Range("E4:H80"), 4, False)
        MsgBox "getharga = " & getharga & " for i = " & i
        If jdiskon = "Nett" Then
            ws1.Range("M" & i).Value = getharga - (getharga * diskon)
            ws1.Range("L25").ClearContents
        ElseIf jdiskon = "Pot" Then
            ws1.Range("M" & i).Value = getharga
            ws1.Range("L25").Value = diskon
        ElseIf jdiskon = "Diskon Kitir" Then
            ws1.Range("M" & i).Value = getharga
            ws1.Range("L25").ClearContents
        Else
            MsgBox "jdiskon = " & jdiskon & " for i = " & i
        End If
    Next

End Sub

(PS: I do not currently have a system with Excel, so this code may need little adjustments).

The code `Range("D" & i) & Range("E" & i)` will work fine (but fully qualifying them would be better) if OP is trying to concatenate the values from those two cells for use in the VLOOKUP function. — ChipsLetten, Jun 01 '15 at 19:01
@ChipsLetten : could u explain more about what u mean with "qualifying"? — uniks, Jun 04 '15 at 04:09
If you explicitly state the workbook and worksheet to which the range belongs. 'Workbooks("book_name.xlsm").Worksheets("sheet_name").Range("A1")' — ChipsLetten, Jun 04 '15 at 08:35

score 0 · Accepted Answer · answered Jul 30 '15 at 07:07

0

sorry for late review, i change my "dim diskon as Range" into "dim diskon as Variant" n then my code works perfectly. Thanks for your effort to help me.

answered Jul 30 '15 at 07:07

uniks

59
8

calculating loop didn't work vba

2 Answers2