Reputation: 315
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.
Upvotes: 3
Views: 59676
Reputation: 315
Thank you for the hint Axel. I never have seen this before, but it works great! This did the trick:
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
But I also really like the answers of Brett . That is a nice new way to approach it.
Upvotes: 0
Reputation: 55692
Hard to optimise without seeing your full code but for this portion:
AND
into two IF
s as VBA doesn't short circuitOR
s, 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
Upvotes: 6