IF and DO UNTIL don't go together ???

[MikeCDPQ]

This piece of script is within a more elaborate one is also withing a DO .... LOOP.

Basically if my sheet is defined as 60 days I want to loop through column C if not 60 days, loop through column B. fairly simple I thought.

I have tried so many things but nothing works. I get error messages: DO WITHOUT LOOP, LOOP WITHOUT DO, etc. one after the other.

What wrong with this code ?


If DaysAged = 60 Then
Do Until Range("c" + CStr(i)) = "" 'Do until record is empty
CurrentVendorNo = Range("c" + CStr(i)).Value 'Compare both records
NextVendorNo = Range("c" + CStr(i + 1)).Value
Else

Do Until Range("B" + CStr(i)) = "" 'Do until record is empty
CurrentVendorNo = Range("B" + CStr(i)).Value 'Compare both records
NextVendorNo = Range("B" + CStr(i + 1)).Value

End If

Thanks for your suggestions.

Mike

 

[Molby]

It should read:

If DaysAged = 60 Then
Do Until Range("c" + CStr(i)) = "" 'Do until record is empty
CurrentVendorNo = Range("c" + CStr(i)).Value 'Compare both records
NextVendorNo = Range("c" + CStr(i + 1)).Value
Loop
Else

Do Until Range("B" + CStr(i)) = "" 'Do until record is empty
CurrentVendorNo = Range("B" + CStr(i)).Value 'Compare both records
NextVendorNo = Range("B" + CStr(i + 1)).Value
Loop
End If

Regards,

Ian

 

[MikeCDPQ]

Hi Molby,

Thanks for the suggestion but it does not work. I had tried something similar but since this piece is imbedded in a DO ... LOOP, I get a message LOOP WITHOUT DO which I do not get if I exclude this IF.. DO WHILE portion.

Any other idea of what the problem could be ???

Thanks for you time.

Mike

 

[RobBroekhuis]

Which "loop" is highlighted when you get the error? Molby's code should work just fine, so there must be something going on with the outside loop. You can post the relevant bits here for us to have a gander at.


Rob

 

[TonyJollans]

Hi Mike,

Molby's code appears correct; if it doesn't work in your context then it seems probable that your context is wrong and we'll need to see it to pinpoint the error. Can you post the whole thing?

You might, however, be able to simplify your code and get rid of the IF if you use the Cells Property instead of Range ..

Col = 2 - (DaysAged = 60)
Do Until Cells(i, Col) = ""
    CurrentVendorNo = Cells(i, Col).Value
    NextVendorNo = Cells(i+1, Col).Value
Loop

Enjoy,
Tony

 

[PhilipAyres]

How about this - decide which column you want to work with first and use the same bit of code to loop through it ?


If DaysAged = 60 Then
ncol = 3'C
Else
ncol = 2'B
End If

With Columns(ncol)
Do Until Cells(i) = "" 'Do until record is empty
CurrentVendorNo = Cells(i).Value 'Compare both records
NextVendorNo = Cells(i + 1).Value
Loop
End With

 

[MakeItSo]

Your code might just not be "well-formed", to say it with a TAG-analogon.
Have you nested your do / if:

do
...
if
...
loop
...
end if

if yes: that won't go. Check that you close the innermost-block before you close the next one, so that it always reads
do
..
if
..
end if
..
loop

Guess, you got something error like that...

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MikeCDPQ]

Thanks everyone for your good suggestions.

Indeed the code provide by Molby was good. I fixed a little something else and it now works.

Thanks a bunch everybody.

Mike