Help with loops

[zoepal]

Dear all,

Im having issues to make my code run in a loop. However it doesnt work. Could you please let me know why is not working?*and how it should be to work? The code is really simple, i want to make some if statements on column B and if they are true to fill the column D. I?*just want to make it looping but i cant find the error.....

Your support is much appreciated,

[Sintek]

There are so many more simplistic solutions...But for this purpose...I'm off to bed...will post others in AM...
Or OP's might jump in...

Please Login or Register  to view this content.

[Marc L]

Could you please let me know why is not working?

'Cause your Vretail variable content has no sense as allocated outside the loop …

and how it should be to work?

If you really want to use a loop :

Please Login or Register  to view this content.

But according to Excel basics it should be directly achieved without any VBA code just with a beginner level formula ‼

When really a VBA procedure is necessary so rather than an useless loop just using a formula :

Sub Demo1() 
   With Range("D2", [A1].End(xlDown)(1, 4)) 
       .Formula = "=IF(LEFT(B2,2)=""Fl"",""Outlet"",IF(LEFT(B2,3)=""PAY"",""Ecom"",IF(LEFT(B2,2)=""RB"",""Resurs Bank"","""")))" 
       .Formula = .Value 
   End With 
End Sub  

Or when the direct formula evaluation is possible like in this VBA demonstration :

Sub Demo2() 
    Const F = "IF(LEFT(#,2)=""Fl"",""Outlet"",IF(LEFT(#,3)=""PAY"",""Ecom"",IF(LEFT(#,2)=""RB"",""Resurs Bank"","""")))" 
    With Range("B2", [B1].End(xlDown)):  .Columns(3) = Evaluate(Replace(F, "#", .Address)):  End With 
End Sub  

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

[Sintek]

Yep...These...were what I was referring to...

[Marc L]

I was waiting you with a less basics formula - if all can be tested with only two letters - than the one in Demo1,
I did not post it as it can't be directly evaluated …

[zoepal]

Thanks to both, the formula worked good but it in reality is a much longer formula. Im testing around 11conditions so the formula looked quite bulkie. But if it runs faster then I will go for it. =)

[Marc L]

So rather to hardcode the conditions why not just using a Settings worksheet holding a conversion table ?
Then just using an easy VLOOKUP formula for example you can get rid of any VBA code !
If really a VBA procedure is necessary then the formula should be easier than my demonstration …

[kvsrinivasamurthy]

Try this code . You can add any number of conditions using ".ADD" command

Please Login or Register  to view this content.

[JohnTopley]

VLOOKUP as per Marc's recommendation

Please Login or Register  to view this content.