英文:
VBA less than operator for cells.value not working correctly
问题
我尝试在Excel VBA中使用常规的小于运算符输出一个值。因此，我希望在一个单元格的值小于8且大于0时每次输出值18。否则将打印“Empty”。这仅适用于某些列不为空的特定行。以下是我的代码片段：
活动工作簿.Sheets(1).Columns(22).NumberFormat = "0.00"
活动工作簿.Sheets(1).Columns(23).NumberFormat = "0.00"
对于x = 2至LastRow
如果不是IsEmpty(Cells(x, "E")) And Not IsEmpty(Cells(x, "F")) And Not IsEmpty(Cells(x, "G")) And Not IsEmpty(Cells(x, "H")) And Not IsEmpty(Cells(x, "I")) And Not IsEmpty(Cells(x, "J")) Then
如果Cells(x, "V").Value <= "8" And Cells(x, "V").Value > "0" Then
Cells(x, "AA").Value = "18"
否则: Cells(x, "AA").Value = "Empty"
结束如果
结束如果
下一个x
该代码仅适用于值为9的单元格，除此之外，即使数字远高于8，它始终输出18。
我还检查了调试器，它显示类型为Value/Double且单元格的正确值。但无论如何，宏都会输入18。
英文:
I try to give out a value using usual less operator in excel VBA. So I would like to give out the value 18 every time the value in one cell is less than 8 and greater than 0. Else "Empty" shall be printed out. This shall only work for specific rows where some columns are not empty. Here my code snippet:
ActiveWorkbook.Sheets(1).Columns(22).NumberFormat = "0.00"
ActiveWorkbook.Sheets(1).Columns(23).NumberFormat = "0.00"
For x = 2 To LastRow
If Not IsEmpty(Cells(x, "E")) And Not IsEmpty(Cells(x, "F")) And Not IsEmpty(Cells(x, "G")) And Not IsEmpty(Cells(x, "H")) And Not IsEmpty(Cells(x, "I")) And Not IsEmpty(Cells(x, "J")) Then
If Cells(x, "V").Value <= "8" And Cells(x, "V").Value > "0" Then
Cells(x, "AA").Value = "18"
Else: Cells(x, "AA").Value = "Empty"
End If
End If
Next x
The code only works for cells with value 9, other than that it always gives out 18, even though the number is much higher than 8.
I check also the debugger and it says Value/Double as type and the correct value of the cells. But anyways the macro enters 18.
答案1
得分: 0
你在比较和赋值语句中使用了字符串值（"8"，"0"，"18"，"Empty"），而不是数值。这导致意外的结果，因为字符串比较与数值比较不同。
尝试使用数值而不是字符串。此外，你可以使用"And"运算符简化代码，将单元格为空的检查组合在一起，并使用ElseIf语句避免不必要的比较。下面是更新后的代码片段：
ActiveWorkbook.Sheets(1).Columns(22).NumberFormat = "0.00"
ActiveWorkbook.Sheets(1).Columns(23).NumberFormat = "0.00"
For x = 2 To LastRow
If Not IsEmpty(Cells(x, "E")) And Not IsEmpty(Cells(x, "F")) And Not IsEmpty(Cells(x, "G")) And Not IsEmpty(Cells(x, "H")) And Not IsEmpty(Cells(x, "I")) And Not IsEmpty(Cells(x, "J")) Then
If Cells(x, "V").Value > 0 And Cells(x, "V").Value < 8 Then
Cells(x, "AA").Value = 18
ElseIf Cells(x, "V").Value >= 8 Then
Cells(x, "AA").Value = "Empty"
End If
End If
Next x
英文:
You are using string values ("8", "0", "18", "Empty") in your comparison and assignment statements instead of numeric values. This is causing unexpected results as string comparison is different from numeric comparison.
Try using numeric values instead of strings. Also, you can simplify your code using the "And" operator to combine the cell empty checks, and use the ElseIf statement to avoid unnecessary comparisons. Here's an updated code snippet:
ActiveWorkbook.Sheets(1).Columns(22).NumberFormat = "0.00"
ActiveWorkbook.Sheets(1).Columns(23).NumberFormat = "0.00"
For x = 2 To LastRow
If Not IsEmpty(Cells(x, "E")) And Not IsEmpty(Cells(x, "F")) And Not IsEmpty(Cells(x, "G")) And Not IsEmpty(Cells(x, "H")) And Not IsEmpty(Cells(x, "I")) And Not IsEmpty(Cells(x, "J")) Then
If Cells(x, "V").Value > 0 And Cells(x, "V").Value < 8 Then
Cells(x, "AA").Value = 18
ElseIf Cells(x, "V").Value >= 8 Then
Cells(x, "AA").Value = "Empty"
End If
End If
Next x
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论