| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arguments within loop
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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" wrote in message ... 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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Arguments within loop
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
![Marc Gendron[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
