Arguments within loop

M

Marc Gendron

Hi gurus,

I have a small bit of code that works fine, but when I try to "improve" it,
I get a "Wend without While" message.

Here's my inner loop:

While nextCell.Value = myCell.Value

Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)

Toto = Toto + 1
conte = conte + 1
Set myCell = Cells(conte, 3)
Set nextCell = Cells(conte + 1, 3)
Wend


Simple enough !
If I try to make it fancy and replace the line that begins with Cells with
this IF block below, then I get an error message (Wend without While)
What gives ?

If counter < 10 Then
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
If counter >= 10 And counter < 100 Then
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
If counter >= 100 And counter < 1000 Then
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
If counter >= 1000 Then
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
End If

Thanks in advance!
Marc
 
R

Ryan H

Try this,

If counter < 10 Then
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
If counter >= 10 And counter < 100 Then
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
End If
If counter >= 100 And counter < 1000 Then
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
End If
If counter >= 1000 Then
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
End If
End If


If this helps, click "YES" below.
 
S

Sam Wilson

You need to either:

a) Use this syntax
If ... then
Do stuff
End if

b) Or this syntax
If ... then do stuff


Ie:

If counter >= 10 And counter < 100 Then
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
End If

or

If counter >= 10 And counter < 100 Then Cells(conte + 1, 2) = "VBLT-000" &
counter & Chr(Toto)

You currently have a mix of both, ie on multiple lines but without a closing
'end if'

Sam
 
J

joel

You need an End IF for each IF. Here are 3 methods you can use


If counter < 10 Then
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
end if
If counter >= 10 And counter < 100 Then
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
end if
If counter >= 100 And counter < 1000 Then
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
end if
If counter >= 1000 Then
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
End If


or

select Case Counter
Case is < 10
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
Case is < 100
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
Case is < 1000
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
Case Else
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
end Select


or
Cells(conte + 1, 2) = "VBLT-" & String(5 - Len(Counter), "0") &
_
counter & Chr(Toto)
 
R

Rick Rothstein

One more way (another one-liner like your last example) that you missed...

Cells(conte + 1, 2) = Format(counter, "VBLT-00000") & Chr(Toto)
 
R

Ryan H

You can try the Select Case statement too.

Select Case True
Case counter < 10
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)

Case counter >= 10 And counter < 100
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)

Case counter >= 100 And counter < 1000
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)

Case counter >= 1000
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
End Select

Let me know if this helps! If so, click "YES" below.
 
M

Marc Gendron

I knew the word "Guru" what right...not only did you solve my problem, but
you taught me too....thanks for the time guys !

Rick Rothstein said:
One more way (another one-liner like your last example) that you missed...

Cells(conte + 1, 2) = Format(counter, "VBLT-00000") & Chr(Toto)

--
Rick (MVP - Excel)


joel said:
You need an End IF for each IF. Here are 3 methods you can use


If counter < 10 Then
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
end if
If counter >= 10 And counter < 100 Then
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
end if
If counter >= 100 And counter < 1000 Then
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
end if
If counter >= 1000 Then
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
End If


or

select Case Counter
Case is < 10
Cells(conte + 1, 2) = "VBLT-0000" & counter & Chr(Toto)
Case is < 100
Cells(conte + 1, 2) = "VBLT-000" & counter & Chr(Toto)
Case is < 1000
Cells(conte + 1, 2) = "VBLT-00" & counter & Chr(Toto)
Case Else
Cells(conte + 1, 2) = "VBLT-0" & counter & Chr(Toto)
end Select


or
Cells(conte + 1, 2) = "VBLT-" & String(5 - Len(Counter), "0") &
_
counter & Chr(Toto)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=160659

Microsoft Office Help

.
 
J

joel

Ryan: See the way I did the Select Case. Since the case statements are
tested in order you don't have to include both the min and max values in
the cae range. It is a litle harder to read the code like you did it
than the way I did it. See my posting for more details.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top