dashes and commas in my array variable

[jkicker]

so i currently have a macro where i push a button on the spreadsheet and input a row number in the dialogue box that pops up, and then my macro produces a form with the row info. i want to update it so that i can input more than 1 row at time, using a dash (-) to indicate a series of rows, and a comma (,) to break these series or individual rows apart (exactly how it works when you specidfy which pages to print, for example).

Sub BT
Dim rows as Variant
rows = InputBox("Enter Row Number. Only 1 row please.")
'rest of code fills out form
End Sub

now, i know i will need to include a for loop. do i need to change my variable to an array? what type of variable do i set it as? would this be correct:

Dim rows() as String
Dim var as Variant
rows() = InputBox("Enter Row Number. For multiple rows, you can include a range such as 1-10, or you can list the rows seperated by a comma, or do both.")
rows = split(rows, ",")
For Each var in rows
'rest of code fills out form
Next var

this would work for separating by commas. i don't know how to deal with the dash (-). please bestow me with epic knowledge.

 

[Eric W]

Maybe something like this:

Sub test1()

    Dim Myrows As String, row1 As Variant, row2 As Variant
    Dim var As Variant
    Dim i As Long, lower As Long, upper As Long
    
    Myrows = InputBox("Enter Row Number. For multiple rows, you can include a range such as 1-10, or you can list the rows seperated by a comma, or do both.")
    row1 = Split(Myrows, ",")
    For Each var In row1
        row2 = Split(var, "-")
        lower = row2(0)
        upper = row2(0)
        If UBound(row2) > 0 Then upper = row2(1)
        For i = lower To upper
            'rest of code fills out form
        Next i
    Next var


End Sub

 

[jkicker]

wouldn't i need to dim row1 and row2 as arrays? ie row1() and row2()
as of now, i'm using rows as my row variable. i'll need to change that in my code. do i change it to row2(i)?

 

[jkicker]

wouldn't i need to dim row1 and row2 as arrays? ie row1() and row2()
as of now, i'm using rows as my row variable. i'll need to change that in my code. do i change it to row2(i)?

ok, i was getting a type mismatch so i changed all my variables to variants and did not dim as an array. i changed my row variable to i, not row2(i).

 

[Eric W]

I'm not sure where you're at, did you get it working?

You can Dim any variable as a variant, but it's usually better to Dim it as what it's going to hold. The result of a Split must be a variant, or Row1() as String. If you use an InputBox, it should be a string, although if you expect the response to be a number, you can pick Long or Double, or some such. A control variable in a For Each loop must be a variant.

It's also a good idea to avoid naming variables the same as a reserved word in VBA, to avoid confusion. "rows" can be used like MyRange.Rows.Count, so in my example, I changed Rows() to Row1. I admit the names I used in my quick example weren't the best though.

 

[jkicker]

yeah i got it working, but i was getting a type mismatch when MyRows was a string and row1 was a variant. i don't understand why, but i just put everything as a variant and it worked.