Receiving Compile Error When Adding a Loop Inside Another Loop

[ashperson95]

Okay, so I have a block of code that asks the user for several inputs and puts them into their corresponding cells in a table on the worksheet. Here's a look at it:

locationNum = 1
For Each InfoInput In Range("Table2[Location]")


InfoInput.Value = InputBox("Name of location " & locationNum, "Name of Location")
fixtures = InputBox("How many fixtures does location " & locationNum & " have?")


InfoInput.Offset(, 1).Value = fixtures


LightTypes.Show
InfoInput.Offset(, 2).Value = LightTypes.ComboBox1.Value


hoursDay = InputBox("How many hours a day do the fixtures in location " & locationNum & " run?")
InfoInput.Offset(, 3).Value = hoursDay


locationNum = locationNum + 1
Next InfoInput

Now, the problem I'm having is when I go to check that the input the user gives is valid. I'm only on the first one, the fixtures variable. Everything was working fine, but when I added in the code that's below and tried to run it, I received a compile error that says "Next without For". I'm not sure where I need to add in the For.

fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
If Not IsNumeric(fixtures) Then
Do Until IsNumeric(fixtures)
fixtures = InputBox("Sorry, that's not a valid entry. How many fixtures does location " & locationNum & " have?")
Loop

Any help would be appreciated!

 

[hiker95]

ashperson95,

It is difficult to say because you have not posted all your macro code, but:

It looks like you are missing an End If

Try:

fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
If Not IsNumeric(fixtures) Then
  Do Until IsNumeric(fixtures)
    fixtures = InputBox("Sorry, that's not a valid entry. How many fixtures does location " & locationNum & " have?")
  Loop
End If

 

[ashperson95]

Wow, I feel stupid for that mistake. Thank you. But now, when I purposely enter an invalid entry for that input, I get a type-mismatch error instead of my input box.

 

[ashperson95]

Here is the entire code for the macro, so you can better understand what's going on.

Sub EnterLocationInfo()
Dim locations As Integer
Dim lightrows As Long
Dim locationNum As Integer
Dim fixtures As Integer
Dim hoursDay As Integer
lightrows = Range("Table2").Rows.Count
locations = Application.InputBox("How many locations do you have?", "Location Input", , , , , , Type:=1)


If locations > lightrows Then
Do Until locations = lightrows
ListObjects("Table2").ListRows.Add
AlwaysInsert = True
lightrows = Range("Table2").Rows.Count
Loop
End If


If locations < lightrows Then
Do Until locations = lightrows
ActiveSheet.ListObjects("Table2").ListRows(lightrows).Delete
lightrows = Range("Table2").Rows.Count
Loop
End If


LightTypes.ComboBox1.List = Sheets("Light Bulbs and Fixtures").Range("Table1[Type of Light]").Value


locationNum = 1
For Each InfoInput In Range("Table2[Location]")


InfoInput.Value = InputBox("Name of location " & locationNum, "Name of Location")
fixtures = InputBox("How many fixtures does location " & locationNum & " have?")


If Not IsNumeric(fixtures) Then
Do Until IsNumeric(fixtures)
fixtures = InputBox("Sorry, that's not a valid entry. How many fixtures does location " & locationNum & " have?")
Loop
End If


InfoInput.Offset(, 1).Value = fixtures


LightTypes.Show
InfoInput.Offset(, 2).Value = LightTypes.ComboBox1.Value


hoursDay = InputBox("How many hours a day do the fixtures in location " & locationNum & " run?")
InfoInput.Offset(, 3).Value = hoursDay


locationNum = locationNum + 1
Next InfoInput
End Sub

 

[hiker95]

ashperson95,

Here is the entire code for the macro, so you can better understand what's going on.

Without having your workbook, I do not have a clue as to what you are trying to do.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Andrew Poulsom]

Try this way:

    Do
        fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
    Loop Until IsNumeric(fixtures)

 

[ashperson95]

hiker95,
I'd be happy to send you the workbook if you'd like.


Andrew,
I still received a type mismatch error instead.

 

[Andrew Poulsom]

Make sure that you declare fixtures as Variant.

 

[hiker95]

ashperson95,

It looks like you are in good hands with Andrew Poulsom.

hiker95,
I'd be happy to send you the workbook if you'd like.

If Andrew Poulsom is not able to solve your request, then:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.