I have multiple sheets in a workbook. I am trying to total columns D-G and them apply a variable if..then statement to the results.
我在工作簿中有多个工作表。我试图将列D-G总计,并将变量if..then语句应用于结果。
Using the below code provide a total but I can't figure out how to get the "if" to work right and display results.
使用下面的代码提供了一个总数,但我无法弄清楚如何让“if”正常工作并显示结果。
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 3
For Each ws In Worksheets
Set EndCell = ws.Cells(Rows.Count, "c").End(xlUp).Offset(1, 1)
If EndCell.Row > StartRow Then
EndCell.Resize(, 4).Formula = "=SUM(R" & StartRow & "C:R[-1]C)"
End If
If EndCell.Row >= 0 Then J3 = "6 Free Bottles"
If EndCell.Row >= 1000 Then
J2 = Formula = ((EndCell.Row) * (0.05))
J3 = "5% Discount"
ElseIf EndCell.Row >= 3000 Then
Range(J2) = Formula = ((EndCell.Row) * (0.1))
Range(J3) = "10% Discount"
End If
Next ws
1 个解决方案
#1
0
When you want to change the value of a cell, you aren't able to use J3 = "6 Free Bottles". Excel thinks that J3 is a variable. Use Cells(3, "J").Value = "6 Free Bottles". You could also use Range("J2").Value =. Range(J2) = tries to set the Range object equal to something, which will not do what you want.
如果要更改单元格的值,则无法使用J3 =“6个*瓶”。 Excel认为J3是一个变量。使用单元格(3,“J”)。值=“6个*瓶”。你也可以使用Range(“J2”)。Value =。范围(J2)=尝试将Range对象设置为等于某个东西,这将无法执行您想要的操作。
Separate note that will cause problems: You are also missing an End If for one of your If statements.
单独的注释会导致问题:对于一个If语句,您也缺少End If。
#1
0
When you want to change the value of a cell, you aren't able to use J3 = "6 Free Bottles". Excel thinks that J3 is a variable. Use Cells(3, "J").Value = "6 Free Bottles". You could also use Range("J2").Value =. Range(J2) = tries to set the Range object equal to something, which will not do what you want.
如果要更改单元格的值,则无法使用J3 =“6个*瓶”。 Excel认为J3是一个变量。使用单元格(3,“J”)。值=“6个*瓶”。你也可以使用Range(“J2”)。Value =。范围(J2)=尝试将Range对象设置为等于某个东西,这将无法执行您想要的操作。
Separate note that will cause problems: You are also missing an End If for one of your If statements.
单独的注释会导致问题:对于一个If语句,您也缺少End If。