IF statement not working

[techie88]

For some reason my if statement does not obey the And clause in the following code:

Sub Method2()

Dim i, j, k As Integer
Dim fluidIDs As Variant
Dim fID As Integer
Dim fType As String
i = 2
j = 2
fluidIDs = Array(207 ... omitted for convenience)

Do While i < 4062
Workbooks("WorkBook1.xlsm").Sheets("WorkSheet1").Activate
fID = Cells(i, 3)
fType = Cells(i, 4)

For k = 0 To 65
If ((fType <> "InputSelection" Or fType <> "MealBar" Or fType <> "CurrentMeal") And fluidIDs(k) + 100 = fID) Then
Range("A" & i & ":E" & i).Select
Selection.Copy
Workbooks("WorkBook1.xlsm").Sheets("WorkSheet2").Activate
ActiveSheet.Cells(j, 30).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
j = j + 1
Exit For
End If
Next k
i = i + 1
Loop
'
End Sub


But "if" obeys the And clause in this code

Sub Method1()

Dim i, j, k As Integer
Dim fluidIDs As Variant
Dim fID As Integer
Dim fType As String
i = 2
j = 200
fluidIDs = Array(207 ...)

Do While i < 5382
Workbooks("WorkBook1.xlsm").Sheets("WorkSheet1").Activate
fID = Cells(i, 3)
fType = Cells(i, 4)

For k = 0 To 65
If (fluidIDs(k) + 100) = fID And (fType Like "InputSelection" Or fType Like "MealBar" Or fType Like "CurrentMeal") Then
Range("A" & i & ":E" & i).Select
Selection.Copy
Workbooks("WorkBook1.xlsm").Sheets("WorkSheet2").Activate
ActiveSheet.Cells(j, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
j = j + 1
Exit For
End If
Next k
i = i + 1
Loop
'
End Sub

I want to fix the if statement in the first code in order to get what I need. Any suggestions?

 

[Welshbird]

I would try doing the second half of your AND statement first, and then follow it by teh OR statement.
So, it becomes:

    If fluidIDs(k) + 100 = fID AND (fType <> "InputSelection" Or fType <> "MealBar" Or fType <> "CurrentMeal") Then

I don't understand why the logic seems to work better tha way, but somehow it seems to.


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[combo]

In the first procedure the condition:
(fType <> "InputSelection" Or fType <> "MealBar" Or fType <> "CurrentMeal")
is always true, so only the second argument of 'And' changes the whole condition (fluidIDs(k) + 100 = fID). Is it what you intend?
If so, add break points and check input arguments in the condition.

BTW, there are a lot of arguments and tips why and how avoid activating and selecting things. It makes the code faster, clearer, usually simpler and more secure.

combo

 

[strongm]

As combo hints, your first If statement is incorrect for what it looks like you are trying to do. Change the ORs to ANDs

 

[Andrzejek]

Instead of:
[tt]
If (fType <> "InputSelection" Or fType <> "MealBar" Or fType <> "CurrentMeal")
[/tt]
either with OR or AND, you may want to do this:
[tt][blue]
If InStr("*InputSelection*MealBar*CurrentMeal*", "*" & fType & "*") Then[/blue]
[/tt]
Once you see this logic - it is a lot easier to keep it clean, add to it, and read it.

Also, you do know in your code:
[tt]
Dim i, j, k As Integer
[/tt]
You actually have:
[tt]
Dim [blue]i As Variant, j As Variant[/blue], k As Integer
[/tt]
Is that what you want?


Have fun.

---- Andy

 

[strongm]

>Also, you do know in your code:

Psst! We told 'em that back in thread707-1659441 ;-)