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

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.
--
Cheers,
Ryan


"Marc Gendron" wrote:

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

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


"Marc Gendron" wrote:

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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/sh...d.php?t=160659
>
> Microsoft Office Help
>

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.
--
Cheers,
Ryan


"Marc Gendron" wrote:

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

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > 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/sh...d.php?t=160659
> >
> > Microsoft Office Help
> >
>
> .
>

You are right. I just didn't take notice.
--
Cheers,
Ryan


"joel" wrote:

>
> 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.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160659
>
> Microsoft Office Help
>
> .
>