Multiplying an integer with a currency in Excel VBA

Question

In my excel table i have one row (5th row) as number of items and another (6th row) as the price of the items. For example i want to multiply 200 with $56.50 but I am having a problem with this script. Can anyone please help.

Sub calcprice()
    Dim i As Integer
    Dim iRowNumber As Integer   ' Integer to store result in
    Dim val As Double

    iRowNumber = InputBox(Prompt:="Number of Rows", _
          Title:="Rows: ", Default:="# of Rows")
    For i = 1 To iRowNumber
        If Cells(i, 5).Value >= 0 And Cells(i, 6).Value >= 0 And IsEmpty(Cells(i, 5)) = False And IsEmpty(Cells(i, 5)) = False Then
            val = FormatCurrency(Cells(i, 5).Value) * Cells(i, 6).Value
            Cells(i, 7).Value = val
        End If
    Next i
End Sub

it says runtime error 13
type mismatch here is the image: it says currency

Here is the link: https://www.dropbox.com/s/lla2cuz8hqu5qyp/test.xlsm
also i cannot use the =a*b i have to use macros!

Why are you using FormatCurrency to convert a value to a string, and then multiplying the string by a number? — Charles Williams, Nov 03 '12 at 17:09
how would i convert both of the cells(i,5).value and cells(i, 6).value both to doubles so that i can mutiply them. — user1796681, Nov 04 '12 at 15:41
All numbers (numbers, dates, times, currency etc) in Excel are held as doubles regardless of how they are formatted. You don't need to do any conversion, particularly if you use .Value 2 rather than .Value — Charles Williams, Nov 05 '12 at 00:32

score 3 · Accepted Answer · answered Nov 04 '12 at 00:59

3

You don't need a loop

You can work with a single shot range in colunm G that

Adds a formula from G5 to the user entered iRowNumber to test whether a result > 0 happens in each row (or adds "" for a 0 result)

overwrite the formulae with the values

Sub calcprice()
    Dim iRowNumber As Long   ' Integer to store result in        
iRowNumber = InputBox(Prompt:="Number of Rows", _
      Title:="Rows: ", Default:="# of Rows")

With Range(Cells(5, 7), Cells(iRowNumber, 7))
.FormulaR1C1 = "=IF(N(RC[-1]*RC[-2]),RC[-1]*RC[-2],"""")"
.Value = .Value
End With
End Sub

answered Nov 04 '12 at 00:59

brettdj

54,857
16
114
177

+1 don't use loops unless you need them, i.e. not a tenth as often as you think you do. – nutsch Nov 04 '12 at 07:10
How would i do it with a loop. I dont understand 'with' statments yet so can you show me how to do it in a for loop? – user1796681 Nov 04 '12 at 15:41
Rather than `With` I could have used these two longer statements, `Range(Cells(5, 7), Cells(iRowNumber, 7)).FormulaR1C1 = "=IF(N(RC[-1]*RC[-2]),RC[-1]*RC[-2],"""")"` `Range(Cells(5, 7), Cells(iRowNumber, 7)).Value = .Value`. I advise you not to go the loop way - its inefficient. I can talk you through these two lines in more detail if that helps – brettdj Nov 05 '12 at 08:29

Artem · Answer 2 · 2012-11-03T16:20:42.973

0

Try this out. Remember to format the 5th cell and the 6th cell to the currency you like!

    Sub calcprice()

    Dim iRowNumber As Integer
    Dim val As Double


    iRowNumber = InputBox(Prompt:="Number of Rows", _
          Title:="Rows: ", Default:="# of Rows")


    For i = 1 To iRowNumber
        If Cells(i, 5).Value >= 0 And Cells(i, 6) >= 0 Then
            If Cells(i, 6).Value > 0 Then
                val = Cells(i, 5).Value * FormatCurrency(Cells(i, 6).Value)
                Cells(i, 7).Value = val
            End If

        End If
    Next i
End Sub

edited Nov 03 '12 at 16:20

answered Nov 03 '12 at 15:58

Artem

1,000
1
15
30

I don't think there is need for the `Dim i As Integer`, it is declared in the for-loop. – Artem Nov 03 '12 at 16:05
However, it does not work when there is no value in the 5th row. – Artem Nov 03 '12 at 16:14
Ah, wait, I thought it was vise versa. Then it is just to change the place of FormatCurrency. – Artem Nov 03 '12 at 16:19
Could you please try the newer version of the code, this ought to work! – Artem Nov 03 '12 at 16:24
Could you please include a copy that file in the post? (parts of it) – Artem Nov 03 '12 at 16:56

score 0 · Answer 3 · answered Nov 05 '12 at 08:02

0

You have For i = 1 to iRowNumber

The image shows that the data starts in row 5. The previous rows contain text.

Therefore, try For i = 5 to iRowNumber

answered Nov 05 '12 at 08:02

barrowc

10,444
1
40
53

Multiplying an integer with a currency in Excel VBA

3 Answers3