Forum Discussion

Copper Contributor

Dec 09, 2021

Getting the user to input a range into VBA

Hey there, I'm looking to do a text to columns transformation within VBA. The data will be coming in various different forms. It will be all on column A, however the data/range we want to capture wil...

Formulas and Functions

Macros and VBA

JKPieterse

Silver Contributor

Dec 09, 2021

Rory123908

To ask a cell from the user is easiest using Application.InputBox:

Option Explicit

Function GetCellFromUser(prompt As String, title As String, default As Range) As Range
    Dim ans As Range
    On Error Resume Next
    Set ans = Application.InputBox(prompt, title, default.Address, , , , , 8)
    Set GetCellFromUser = ans
End Function

Sub Demo()
    Dim rng As Range
    Set rng = GetCellFromUser("Please select the start cell", "Selecting a start cell for the macro", ActiveCell)
    If rng Is Nothing Then
        'user cancelled
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
        rng.Resize(rng.End(xlDown).Row - rng.Row + 1).Select
    End If
End Sub

Rory123908

Copper Contributor

Dec 09, 2021

Hi again, Ive added a line of code to your example to try to solve my problem. Here is the full code: Option Explicit

Function GetCellFromUser(prompt As String, title As String, default As Range) As Range
Dim ans As Range
On Error Resume Next
Set ans = Application.InputBox(prompt, title, default.Address, , , , , 😎
Set GetCellFromUser = ans
End Function

Sub Demo()
Dim rng As Range
Set rng = GetCellFromUser("Please select the start cell", "Selecting a start cell for the macro", ActiveCell)
If rng Is Nothing Then
'user cancelled
Else
'range was selected, now do something, like select all cells to the bottom of that range:
rng.Resize(rng.End(xlDown).Row - rng.Row + 1).Select
rng.TextToColumns , xlDelimited, xlDoubleQuote, True, , , , True

End If

End Sub

It is the part about texttocolumns! For some reason this only applies it to the first cell in the list, and does not apply to all selected cells, do you have any idea why it does this and how I may fix it?

JKPieterse
Silver Contributor
Dec 09, 2021
Change this line:
rng.Resize(rng.End(xlDown).Row - rng.Row + 1).Select
to:
Set rng = rng.Resize(rng.End(xlDown).Row - rng.Row + 1)
then it should work