selecting a range in excel

visual basic question

Sub GetUserRan()
Dim UserRan As Range
Dim nput As Variant
Dim Prompt As String
Dim Title As String

Prompt = "Select a cell for the input."
    Title = "Select a cell"

'   Display the Input Box
    On Error Resume Next
    Set UserRan = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8) 'Range selection
       
            If UserRan Is Nothing Then
        MsgBox "Canceled."
    Else
        GetUserRange UserRan.Range("A1:A2:A3") '= Output
    End If

i am trying to write a VB code that will ask the user to enter the columns in which he wants processed. in the form of A1:B1:C1 or A:B:C. is this possible? if so how can it be done. and after the user has entered it. i want to store them in an array. so that the first index of the array will have lets say "A", the second index "B" and so on.

End Sub
sadupopsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rettiseertCommented:
I don't think you should ask the user to enter it in the form of "A:B:C", that's cofussing, instead you shoul ask him to enter the range in the standard excel form:

For example, for selecting A, B and C columns use "A:C"
For selecting just A use "A:A"
For selecting A, C, and E columns use "A:A,C:C,E:E"

Then try this:

    Dim UserColumnLetters As Variant  '<--- This will be you array with column letters
   
    Dim Prompt As String
    Dim Title As String
    Dim UserInput As String
   
    Dim TempUserColumnLetters As String
    Dim TmpRange As Range
    Dim TmpArray As Variant
   
    Dim I As Long
    Dim Clmn As Range
   

    'Display the Input Box
    Prompt = "Select a cell for the input."
    Title = "Select a cell"
    UserInput = InputBox(Prompt, Title)
   
    If UserInput <> "" Then
       
        'Split text separated by ","
        TmpArray = Split(UserInput, ",")
       
        'Search for columns
        For I = LBound(TmpArray) To UBound(TmpArray)
       
            'Get this range
            Set TmpRange = Nothing
            On Error Resume Next
            Set TmpRange = Range(TmpArray(I))
            On Error GoTo 0
           
            'If the range is valid, get the column letter
            If Err = 0 Then
                For Each Clmn In TmpRange.Columns
                    TempUserColumnLetters = TempUserColumnLetters + Mid(Clmn.AddressLocal, 2, 1) + ","
                Next
            Else
                Err.Clear
            End If
           
        Next
       
        'If there are column letters then convert then into an array
        If TempUserColumnLetters <> "" Then
            UserColumnLetters = Split(Left(TempUserColumnLetters, Len(TempUserColumnLetters) - 1), ",")
           
            'Show results for testing
            For I = LBound(UserColumnLetters) To UBound(UserColumnLetters)
                Debug.Print "UserColumnLetters(" + Format(I) + ")=" + UserColumnLetters(I)
            Next
        End If
       
    End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sadupopsAuthor Commented:
  For Each Clmn In TmpRange.Columns
                    TempUserColumnLetters = TempUserColumnLetters + Mid(Clmn.AddressLocal, 2, 1) + ","
                Next

that line above gives me an error. i just tested it. the for loop stmt.
sadupopsAuthor Commented:
The code above doensn't seem to work
sadupopsAuthor Commented:
can someone please help me figure out what is going on??????????????
Program_PoserCommented:
Sadupops,

Rettiseert's code seems to work fine?  Make sure your input is in the correct format..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.