How can I multiply the price by the correct currency Value

Question

Just started learning VBA

I want to multiply the product price with the currency value to get a new price.

No      Name    Quantity Price Product Currency OrderID       Currency  $
1       Tim     5       5       A       HKD     RX12            HKD 1
2       Alan    6       5       A       HKD     PR22            USD 7.8
3       Alan    2       6       B       USD     PR22            CAN 6
4       Bob     3       5       A       HKD     ED45            
5       Bob     8       8       C       CAN     ED45            
6       Tim     10      6       B       USD     AS63            
7       Rose    12      8       C       CAN     LM36            
8       Cathy   15      6       B       USD     JI48            
9       Rose    2       5       A       HKD     HG54            
10      Tim     8       6       B       USD     VB87

Here's the code I've tried so far. I keep getting object not defined errors. How can I fix this?

Sub NP()

    Dim NP, i As Integer
    NP = Range("L2:L11").Value

    For i = 1 To 10
        P = Cells(4, i)
        curr = Cells(6, i)

        If curr = "HKD" Then
            NP = Range("J2").Value * P    
        ElseIf curr = "USD" Then
            NP = Range("J3").Value * P    
        Else
            NP = Range("J4").Value * P
        End If
    Next i

End Sub

@Clive I rewrote his question, can you take it off of hold so I can answer? — Miles Fett, Sep 04 '19 at 17:33
Note that `Dim NP, i As Integer` only declares `i` as `Integer` but `NP` as `Variant`. In VBA you must specify a type for **every** variable (otherwise it is `Variant` by default): `Dim NP As Double, i As Long` Also [I recommend to use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) and `Double` for `NP`. — Pᴇʜ, Sep 05 '19 at 06:30

score 0 · Answer 1 · answered Sep 05 '19 at 06:40

If the data looks like in the sheet below

You can do that with a simple formula in column L

=INDEX(J:J,MATCH(F:F,I:I,0))*D:D

It uses the MATCH function to find the currency (column F) in the currency list (column I) and return its row number. And combines it with the INDEX function to return the currency factor (column J) of that matched row, to finally multiply it with the price (column D).

score 0 · Answer 2 · answered Sep 05 '19 at 13:47

Try this. Your biggest problem was how you were declaring your variables. Also, the P = 1 + i helps you to avoid running an infinite loop in the first cell. Hope this helps!

Sub NP()

    Dim NP As Long
    Dim i As Long
    Dim P As Long
    Dim curr As String

    For i = 2 To 10
    P = Cells(i, 4)
    curr = Cells(i, 6)
        If curr = "HKD" Then
            NP = Range("J2").Value * P
        ElseIf curr = "USD" Then
            NP = Range("J3").Value * P
        Else
            NP = Range("J4").Value * P
        End If
    P = 1 + i
    Next i

End Sub

How can I multiply the price by the correct currency Value

2 Answers2