Receiving Error 1004 With InputBoxes

[ashperson95]

Hi again,

I am using Input Boxes to fill in information in a table. I'm getting a runtime error 1004 (Application-defined or Object defined) when I tried extending the range one column over and filling that in with an Input Box. I'm not sure what the problem is. Could someone help me?

Sub EnterLocationInfo()Dim locations As Integer
Dim lightrows As Long
Dim InfoInput As Range
Dim locationNum As Integer
Dim fixtures 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

'This block of code is where I'm receiving the error
locationNum = 1
For Each InfoInput In Range("Table2[Location]", "Table2[# of Fixtures]")
InfoInput.Value = InputBox("Name of location " & locationNum, "Name of Location")
fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
InfoInput.Offset(, 1).Value = fixtures
locationNum = locationNum + 1
Next InfoInput
End Sub

 

[JoeMo]

The syntax on your range line is wrong. Try this:

For each InfoInput in Range(Range("Table2[Location]"), Range("Table2[# of Fixtures]"))

 

[ashperson95]

It didn't work, I'm still getting the error :/

 

[JoeMo]

It didn't work, I'm still getting the error :/

On which line?

 

[ashperson95]

On which line?

I know it's in one or all of these lines:

For Each InfoInput In Range(Range("Table2[Location]"), Range("Table2[# of Fixtures]"))
fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
InfoInput.Offset(, 1).Value = fixtures

Because before I inserted the fixtures input box and the offset line, and before I changed the range from one column to two, it was working fine.

 

[JoeMo]

I know it's in one or all of these lines:

For Each InfoInput In Range(Range("Table2[Location]"), Range("Table2[# of Fixtures]"))
fixtures = InputBox("How many fixtures does location " & locationNum & " have?")
InfoInput.Offset(, 1).Value = fixtures

Because before I inserted the fixtures input box and the offset line, and before I changed the range from one column to two, it was working fine.

Did you click Debug to see which line is highlighted?

 

[ashperson95]

It's in this line:

For Each InfoInput In Range(Range("Table2[Location]"), Range("Table2[# of Fixtures]"))

I just tried taking out the second range (so now it's only location on that line), and just using the offset that way, and it works! I'm not sure why but it does. So now it's just this:

locationNum = 1For 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
locationNum = locationNum + 1
Next InfoInput
End Sub