dashes and commas in my array variable

jkicker

Board Regular
Joined
Jun 7, 2018
Messages
77
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).

VBA Code:
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:

VBA Code:
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Maybe something like this:

VBA Code:
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

Board Regular
Joined
Jun 7, 2018
Messages
77
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

Board Regular
Joined
Jun 7, 2018
Messages
77
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
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

Board Regular
Joined
Jun 7, 2018
Messages
77
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top