Arguments within loop

[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

 

[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.

 

[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

 

[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)

 

[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)

 

[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.

 

[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 !

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)

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

.

 

[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.

 

[Ryan H]

You are right. I just didn't take notice.