在我的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 Subit 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 SubYou 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更多推荐
发布评论