IF condition not working

[trebleo]

Good evening all,

I am trying to run a basic IF condition with a loop. But VBA is giving some unexpected results.
See data below, if the data columns become misaligned, then my sincere apologies.

If Distribution channel is 99, I want Comments to say "No Door". If Distribution channel is 0, I want comments to say "Door". Please note the top left "cell" is with 0 value is cell Y3, which is the starting point for my code.


Door Distribution Channel Comments
0 99
960353 0
960353 0
960673 0
960673 0
0 99
0 99

Please Login or Register  to view this content.

For some strange reason, this sub routine changes the value of the distribution channel to value 0, instead of testing whether the value is 0 or 99. This is the outcome of the sub routine.

Door Distribution Channel Comments
0 0 Door
960353 0 Door
960353 0 Door
960673 0 Door
960673 0 Door
0 0 Door
0 0 Door


Could anyone please explain why it is changing the value of the distribution channel instead of testing it? Many thanks!

[SjonR]

Distchannel = ...... instead of .......... = distchannel

[Arkadi]

ActiveCell.Offset(0, 1).Value = DistChannel means that cell is being made the value of DistChannel which is always 0 since no value is ever given to it.

As SjonR said, you need to reverse that since you meant to make DistChannel the value of the cell:

DistChannel = ActiveCell.Offset(0, 1).Value

[Marc L]

I am trying to run a basic IF condition with a loop.

Hi !

Never need a loop when it's basic ! Just use Excel basics like an easy beginner formula :

Sub Demo1() 
    With Range("AA3", [Z2].End(xlDown)(1, 2)) 
        .Formula = "=IF(Z3=0,""Door"",IF(Z3=99,""No Door"",""Error""))" 
        .Formula = .Value 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Marc L]

Or via a bit more advanced way :

Sub Demo2()
    With Range("Z3", [Z2].End(xlDown))
        .Offset(, 1).Value = Evaluate(Replace("=IF(#=0,""Door"",IF(#=99,""No Door"",""Error""))", "#", .Address))
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[trebleo]

Hi Sjon and Arkadi.

Thanks for the quick responses. I was under the impression that it didnt matter whether the variable name was left or right of the = sign. Thanks for correcting this.

[trebleo]

Hi Marc L,

Yes, I was just testing using a basic instruction to tests variables and loops. Im still a beginner with VBA, but I can make sense of your IF formula. My instructions were basic enough not to require variables or loops as you have shown. Thank you!