在Excel VBA中将整数与货币相乘(Multiplying an integer with a currency in Excel VBA)

在Excel VBA中将整数与货币相乘(Multiplying an integer with a currency in Excel VBA)

在我的excel表中，我有一行（第5行）作为项目数，另一行（第6行）作为项目的价格。 例如，我想用200.50美元乘以200，但我遇到了这个脚本的问题。 谁能请帮忙。

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

它说运行时错误13 类型不匹配这里是图像：

这是链接： https ： //www.dropbox.com/s/lla2cuz8hqu5qyp/test.xlsm 我也不能使用= a * bi必须使用宏！

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:

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

最满意答案

你不需要循环

您可以使用colunm G中的单发射程

将G5中的公式添加到用户输入的iRowNumber以测试每行中是否发生结果> 0（或者为0结果添加"" ）

用值覆盖公式

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

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