在VBA中如果还是多个语句 [英] If And Or Multiple Statements in VBA

问题描述

我想将具有14列的Excel文件重新分配到正确的列（具有12.000行）。

为此，我必须使用一些If And Or语句将数字放在矩阵中。但是显然我没有得到正确的东西。

它使我的所有单元格为零，而具有值的单元格应该保持该值。 >

我在哪里出错？：

  For i = 1 To LastRow 
如果Cells（i，8）.Value2 =Then Cells（i，8）.Value2 = 0 
 
如果Cells（i，1）.Value2< 437和细胞（i，5）.Value2 =aa_ 
或细胞（i，5）.Value2 =bb_ 
或细胞（i，5）.Value2 =cc _ 
或单元格（i，5）.Value2 =dd_ 
或单元格（i，5）.Value2 =ee_ 
或单元格（i，5）.Value2 =ff_ 
或单元格（i，5）.Value2 =gg_ 
和Cells（i，7）.Value2 =_ 
然后单元格（i，7 ）.Value2 = 0 
 
 Next i 
  

所以如果单元格包含aa或bb或cc或dd或ee或ff或gg，并且如果细胞应变为0，否则应保持相同的值。之后它应该进入矩阵

 然后Matrixgetallen（i，2）= CDbl（Cells（i，7）.Value2 ）
  

但是我没有设法在同一个if语句中。

如果有6种这样的If语句，那么可能If Then Else不起作用。

解决方案

很难优化，而没有看到你的完整代码，但这部分：

1. 打破 AND 分成两个 IF 作为VBA 不会短路


2. 而不是长序列 OR s，对阵列执行单次测试（数字结果意味着找到确切的字符串）

代码

  i = 1 
 Dim strIN 
 strIN = Array（aaf，bb，cc，dd，ee，ff，gg）
 
如果Cells ，1）.Value2 437然后
如果Len（Cells（i，5））= 0然后
单元格（i，7）.Value2 = 0 
 Else 
如果IsNumeric（Application.Match （i，5），strIN，0））Then Cells（i，7）.Value2 = 0 
 End If 
 End If 
  

I want redistribute an Excel file with 14 Columns to the correct Column (with 12.000 rows).

For this I have to use some If And Or Statements to put the numbers in a matrix. But apparently I don't get the right things out of it.

It makes all my cells zero, while the cells with a value should keep the value.

Where do I go wrong?:

    For i = 1 To LastRow
    If Cells(i, 8).Value2 = "" Then Cells(i, 8).Value2 = 0

    If Cells(i, 1).Value2 < 437 And Cells(i, 5).Value2 = "aa" _
    Or Cells(i, 5).Value2 = "bb" _
    Or Cells(i, 5).Value2 = "cc" _
    Or Cells(i, 5).Value2 = "dd" _
    Or Cells(i, 5).Value2 = "ee" _
    Or Cells(i, 5).Value2 = "ff" _
    Or Cells(i, 5).Value2 = "gg" _
    And Cells(i, 7).Value2 = "" _
    Then Cells(i, 7).Value2 = 0

    Next i

So if the cell contains an aa or bb or cc or dd or ee or ff or gg and is empthy the cell should become 0 otherwise it should stay the same value. After that it should go into a matrix

Then Matrixgetallen(i, 2) = CDbl(Cells(i, 7).Value2)

But I didn't manage to get that in the same if statement.

If have 6 of these kind of If-statements, so probably If Then Else doesn't work.

解决方案

Hard to optimise without seeing your full code but for this portion:

1. Break the AND into two IFs as VBA doesn't short circuit
2. Rather than a long sequence of ORs, do a single shot test against an array (a numeric result means the exact string is found)

code

i = 1
Dim strIN
strIN = Array("aaf", "bb", "cc", "dd", "ee", "ff", "gg")

If Cells(i, 1).Value2 < 437 Then
    If Len(Cells(i, 5)) = 0 Then
        Cells(i, 7).Value2 = 0
    Else
        If IsNumeric(Application.Match(Cells(i, 5), strIN, 0)) Then Cells(i, 7).Value2 = 0
    End If
End If

这篇关于在VBA中如果还是多个语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！