Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Next without For Error

Status
Not open for further replies.
Omnicube

Omnicube

MIS
Nov 7, 2011
40
US
In the following code, I am getting a next without for error after dropping in the 'If IsNumeric(strSplitter(x)) Then' line

Any ideas as to why? The 'Next x' has a corresponding 'For x'

Code: 
Sub ExpandAIM()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lngRowFrom As Long
Dim lngRowTo As Long
Dim x As Integer 'Loop through the array
Dim strSplitter() As String

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set ws2 = Sheet2

lngRowTo = 2
For lngRowFrom = 2 To ws.Range("A65000").End(xlUp).Row
'The "to" part is finding the last row, so you don't have to specify
   If InStr(ws.Cells(lngRowFrom, 1).Value, ",") Then
      strSplitter = Split(ws.Cells(lngRowFrom, 1).Value, ",")
     For x = LBound(strSplitter) To UBound(strSplitter)
            [blue]If IsNumeric(strSplitter(x)) Then[/blue]
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
            Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
            Loop
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
         ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
         ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
         ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
         ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
         lngRowTo = lngRowTo + 1
    Next x
    Else
        Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
        ws2.Cells(lngRowTo, 1).Value = ws.Cells(lngRowFrom, 1).Value
        ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
        ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
        ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
        ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
        Loop
        lngRowTo = lngRowTo + 1
   End If
Next lngRowFrom

End Sub
 
Sort by date Sort by votes


hi,

Don't always believe the error message.

SOMETHING is in error. NOT the for...next

Check out the missing End If
Code: 
            If IsNumeric(strSplitter(x)) Then

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
DOH! Silly me forgetting the End If statement! Now I have a new problem. The values from column 2 - 5 are not being output when there is a delimited string.



Code: 
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lngRowFrom As Long
Dim lngRowTo As Long
Dim x As Integer 'Loop through the array
Dim strSplitter() As String

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set ws2 = Sheet2

lngRowTo = 2
For lngRowFrom = 2 To ws.Range("A65000").End(xlUp).Row
'The "to" part is finding the last row, so you don't have to specify
   If InStr(ws.Cells(lngRowFrom, 1).Value, ",") Then
      strSplitter = Split(ws.Cells(lngRowFrom, 1).Value, ",")
     For x = LBound(strSplitter) To UBound(strSplitter)
            [blue]If IsNumeric(strSplitter(x)) Then
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
            End If[/blue]
            Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
         ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
         ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
         ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
         ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
         Loop
         lngRowTo = lngRowTo + 1
    Next x
            
    Else
        Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
        ws2.Cells(lngRowTo, 1).Value = ws.Cells(lngRowFrom, 1).Value
        ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
        ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
        ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
        ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
        Loop
        lngRowTo = lngRowTo + 1
   End If
Next lngRowFrom

End Sub
 
Upvote 0 Downvote
Figured out the issue described in my last post. However, the the function is still parsing the non numeric values to my output.

An example input is 987654321 (ABC)

In previous examples, using the IsNumeric function on an input such as this (when running Debug.Print "Not a Number") would result in the Immediate window displaying "Not a Number." However, in this situation it is still parsing it to the output. Is this because I did not designate an else in my If Statement? If so, what should I include as an Else to ignore values similar to the value mentioned above.

Code: 
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lngRowFrom As Long
Dim lngRowTo As Long
Dim x As Integer 'Loop through the array
Dim strSplitter() As String

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set ws2 = Sheet2

lngRowTo = 2
For lngRowFrom = 2 To ws.Range("A65000").End(xlUp).Row
'The "to" part is finding the last row, so you don't have to specify
   If InStr(ws.Cells(lngRowFrom, 1).Value, ",") Then
      strSplitter = Split(ws.Cells(lngRowFrom, 1).Value, ",")
     For x = LBound(strSplitter) To UBound(strSplitter)
            'strSplitter = Trim(strSplitter(x))
            If IsNumeric(Trim(strSplitter(x))) Then
            ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
            End If
            Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
         Loop
         ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
         ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
         ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
         ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
         lngRowTo = lngRowTo + 1
    Next x
            
    Else
        Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
        ws2.Cells(lngRowTo, 1).Value = ws.Cells(lngRowFrom, 1).Value
        ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
        ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
        ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
        ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
        Loop
        lngRowTo = lngRowTo + 1
   End If
Next lngRowFrom

End Sub
 
Upvote 0 Downvote


Please explain WHAT it is that you need to do, rather than HOW you think it ought to be accomplished. No code, please. Post examples, the desired result and the logic in plain prose.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
You assign the value to the regardless of whether it is a numeric value or not, as long as the cell contains a null string (so if it doesn't get assigned because the value is numeric, you force it to be assigned to the value anyway)...
Code: 
For x = LBound(strSplitter) To UBound(strSplitter)
            'strSplitter = Trim(strSplitter(x))
            If IsNumeric(Trim(strSplitter(x))) Then
            ws2.Cells(lngRowTo, 1).Value = strSplitter(x) ' if it's numeric assign it here
            End If
            [red]Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
         Loop[/red] ' if the value isn't assigned to the cell, assign it here?
In agreement with what Skip said, it'd be helpful to know what you're actually trying to do.

Hope this helps

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

 
Upvote 0 Downvote
thread tittle said:
Next without For Error
Click to expand...
Mind that the code is structured. After [tt]If IsNumeric(strSplitter(x)) Then[/tt] and before [tt]ElseIf/End If[/tt] the compiler expects a closed block of code, in this case it found an unexpected [tt]Next[/tt], thus the error message.

combo
 
Upvote 0 Downvote



FYI,

When coding block statements like For...Next, If...Then...Else...End If, With...End With etc, I make it a practice to code the entire block structure immediately, and then go back and fill in the details. Consequently, I experience fewer of these kinds of errors.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
I need to parse out 8 - 9 digit values with related numbers in other columns. For example:

Before Parse
Column A Column B Column C Column D
123456789, 987654321 a Apples Books
654987321 b Oranges Movies
123789456 c Carrots DVD
876543210, 223456789 d Bananas CD
654321987, 789456123 e Coconuts HDD

Additionally, I need to exclude anything from column A that might look like this:


741852963 (blah)

Hence the usage of the IsNumeric function.
 
Upvote 0 Downvote
I messed with the placement of the EndIf statement, and I got everything to work.

I am still trying to get the hand of this, but I appreciate everyone's help and comments!

Here is the code BTW:

Code: 
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lngRowFrom As Long
Dim lngRowTo As Long
Dim x As Integer 'Loop through the array
Dim strSplitter() As String

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set ws2 = Sheet2

lngRowTo = 2
For lngRowFrom = 2 To ws.Range("A65000").End(xlUp).Row
'The "to" part is finding the last row, so you don't have to specify
   If InStr(ws.Cells(lngRowFrom, 1).Value, ",") Then
      strSplitter = Split(ws.Cells(lngRowFrom, 1).Value, ",")
     For x = LBound(strSplitter) To UBound(strSplitter)
            If IsNumeric(Trim(strSplitter(x))) Then
            ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
            Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
         ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
         Loop
         ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
         ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
         ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
         ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
         lngRowTo = lngRowTo + 1
         End If
    Next x
    Else
        Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
        ws2.Cells(lngRowTo, 1).Value = ws.Cells(lngRowFrom, 1).Value
        ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
        ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
        ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
        ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
        Loop
        lngRowTo = lngRowTo + 1
        
   End If
Next lngRowFrom

End Sub
 
Upvote 0 Downvote


You have nothing in your most recent sample data that represents what you want to exclude, like 741852963 (blah).

And, what is the AFTER for your exclude example???

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
246
remeng
remeng
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
277
dhookom
dhookom
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
339
sxschech
sxschech
remeng
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
488
strongm
strongm
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
548
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top