检测行中的所有数组元素是否为空

So im trying to write some VBA for my excel spreedsheet.

所以我试着为我的excel spreedsheet写一些VBA。

I have a table with four coloumns, i read each line and I want it to skip that line if coloumn 1 2 or 3 is empty, but instead it exits and doesnt move to the next line.

我有一个有四个颜色的表,我读了每一行,如果coloumn 1 2或3为空,我希望它跳过该行,而是退出并且不会移动到下一行。

  For y = LBound(myArray) To UBound(myArray)
    
    If myArray(y, 1) = "" Then Exit For
        If myArray(y, 2) = "" Then Exit For
            If myArray(y, 3) = "" Then Exit For
            
            
    Attribute = "Animal"

    Value = myArray(y, 3)
    Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)
    
    
    Debug.Print Value

  Next y

Debug.Print "***TEST FINISHED***"

4 个解决方案

#1

Why not make it simple by using OR Try replacing your lines 2-4 conditions by 1 IF combined with AND

为什么不通过使用OR使其变得简单。尝试用1 IF结合AND替换你的2-4行条件

If myArray(y, 1) <> "" And myArray(y, 2) <> "" And myArray(y, 3) <> "" Then

如果myArray(y,1)<>“”和myArray(y,2)<>“”和myArray(y,3)<>“”那么

#2

Running an ever increasing IF test to check every element will get messy.

运行不断增加的IF测试以检查每个元素将变得混乱。

Another option is to

另一种选择是

Slice each row of the array using Index (in the example below I Transpose the array outside of the loop so Index slices a row rather than column)

使用索引切割数组的每一行(在下面的示例中,我将数组转置到循环外部,以便索引切片行而不是列)
Tranpose is used to convert the row slice to a 1D array

Tranpose用于将行切片转换为1D数组
Join makes a string of the row

Join创建一行的字符串

Len then tests if the row is empty (if the string is zero length)

Len然后测试行是否为空(如果字符串为零长度)

code

`test array
myArray = [a1:d100]
myArr2 = Application.Transpose(myArray)

With Application
For y = LBound(myArray) To UBound(myArray)
    If Len(Join(.Transpose(.Index(myArr2, , y)))) = 0 Then
    `row is empty
        Else
    `plan b
    End If
Next y
End With

#3

Exit For attached to each of your conditions is explicitly terminating the For loop, not moving on to the next cycle of the loop. Excel VBA doesn't have a Continue -type option to skip to the next iteration of the loop, so you need to handle this yourself.

退出对于附加到每个条件,显式终止For循环,而不是继续循环的下一个循环。 Excel VBA没有Continue -type选项可以跳到循环的下一次迭代,因此您需要自己处理。

For readability I often code the failure conditions, as you have, then run the code on acceptable conditions under an Else clause:

为了便于阅读,我经常编写故障条件,然后在Else子句下的可接受条件下运行代码:

For y = LBound(myArray) To UBound(myArray)
    
    If myArray(y, 1) = "" Or myArray(y, 2) = "" Or myArray(y, 3) = "" Then 
         ' unsuitable for action
    Else
            
        Attribute = "Animal"

        Value = myArray(y, 3)
        Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)
            Debug.Print Value

        ' TODO: take useful action on the generated string
 
    End If

Next y

    Debug.Print "***TEST FINISHED***"

Because it's not descriptive and can be confused with the cell property, I wouldn't have a variable called Value, either.

因为它不具有描述性并且可以与单元属性混淆,所以我也没有名为Value的变量。

#4

Here is an idea you might use: What I'm doing is adding an if that only executes if none of the three lines are empty. If any of them is empty, the for-loop won't do anything and just skip to next y.

这是你可能会使用的一个想法:我正在做的是添加一个如果仅在三行中没有一行为空时才执行。如果它们中的任何一个为空,则for循环将不会执行任何操作,只是跳到下一个y。

For y = LBound(myArray) To UBound(myArray)

If not(myArray(y, 1) = "" or myArray(y, 2) = "" or myArray(y, 3) = "") Then 
    Attribute = "Animal"

    Value = myArray(y, 3)
    Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)


    Debug.Print Value
End If
Next y
Debug.Print "***TEST FINISHED***"

#1