Capture Cell range from user input in Excel VBA

Question

I receive excel workbooks from various customers containing lists of employees with gender and date of birth columns. They arrive in varying formats. I am creating an excel worksheet that I will add to each of these workbooks which will allow me to apply a series of formulas to the customer data. The problem is that I do not know the location of the date of birth data in advance.

I want a macro to prompt me to select the range for the date of birth values and then place that data range into a cell (F31) on my worksheet so that I can pull it into my other formulas.

The code I created below works, but it does not pull the worksheet tab name along with the range. How can I get the worksheet name along with the cell range?

Sub ChooseDOBRange()
    Dim rng As Range

    Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    rng.Copy

    Worksheets("COVER SHEET").Range("F31") = rng.Address    
End Sub

Siddharth Rout · Accepted Answer · 2015-06-23 21:26:25Z

3

How can I get the worksheet name along with the cell range?

You need rng.Parent.Name

Is this what you are trying?

Sub ChooseDOBRange()
    Dim rng As Range

    On Error Resume Next '<~~ In case user presses Cancel
    Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    On Error GoTo 0

    If Not rng Is Nothing Then
        rng.Copy

        Worksheets("COVER SHEET").Range("F31").Value = _
        rng.Parent.Name & " - " & rng.Address '<~~ Something like "Sheet2 - $C$6:$H$14"
    End If
End Sub

answered Jun 23, 2015 at 21:26

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Byron Wall Over a year ago

I assumed OP wanted a valid range to use with INDIRECT or such. Either way, we're on the same page with Range.Parent.Name.

Byron Wall · Accepted Answer · 2015-06-23 21:28:30Z

3

There are two ways to get this depending on whether or not you want the Workbook name to be in the formula also. I am testing these in the Immediate Window.

Method 1 uses .Address with the External parameter set to True

?ActiveCell.Address(,,,True)

[Book2]Sheet1!$A$1

Method 2 uses the .Address along with the sheet name from Range.Parent.Name where Parent refers to the Worksheet for a Range

?"'" & ActiveCell.Parent.Name & "'!" & ActiveCell.Address

'Sheet1'!$A$1

answered Jun 23, 2015 at 21:28

Byron Wall

4,0202 gold badges16 silver badges31 bronze badges

3 Comments

Siddharth Rout Over a year ago

++ for going the extra mile :)

Jim Mc Over a year ago

Excellent! I used Siddharths code and changed it to DOBrng.Parent.Name & "!" & DOBrng.Address so I could store the proper syntax to use with INDIRECT. It works fine except when I choose a set of ranges that are not contiguous. In that case the worksheet name is not appended to the cell range properly it only appends to the beginning of the string: Sheet2!$C$17:$C$21,$C$24:$C$26 when instead I need it to be Sheet2!$C$17:$C$21,Sheet2!$C$24:$C$26. Any thoughts on how to make it work in that situation?

Byron Wall Over a year ago

INDIRECT does not support discontinuous ranges so you need a different approach. You could iterate through rng.Areas and spit out each area address into its own cell. You could then combine them with an INDIRECT for each area. Best approach really depends on the function you are using around INDIRECT.

Collectives™ on Stack Overflow

Capture Cell range from user input in Excel VBA

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related