Collect range via InputBox on another sheet

[Rudi]

Hi,

I need an application.inputbox to collect the range address from a user selection on another sheet. The problem is that when it returns to the range variable, it drops the sheet name (and only retains the actual range selection). Is there a trick to collect the full address including the sheet name and pass it to the variable?

My code as below:
Set TableArr = Application.InputBox("Select the range to collect from.", "Market Value", Type:=8)

TX.

[HansV]

The range object TableArr includes the full range specification, with the workbook and worksheet. What makes you think the sheet name is dropped?

[Rudi]

Hi,

I am trying to create a Vlookup with prompts to simplify the process for basic users. The macro os almost right except for the tablearray portion. Any help or advice will be great. TX.

Code: Select all

Sub CreateVlookup()
Dim ColTitleRange As Range, ColTitle As String, LookupVal As Range, TableArr As Range, ColNum As Integer, FormulaRange As Range
    Set ColTitleRange = Application.InputBox("Select the cell for the new column title.", "Cell to hold the New Column Title", Type:=8)
    ColTitle = Application.InputBox("Type the name for the column", "Create Market Value Column", "Market Value")
    Set FormulaRange = Application.InputBox("Select the range below the Title where the formula must be entered into.", "Formula Location Range", Type:=8)
    Set LookupVal = Application.InputBox("Select the cell on this sheet containing the unique value in order to determine the market value to return.", "Unique Lookup Value", Type:=8)
    Set TableArr = Application.InputBox("Select the table range, (all rows and columns on the other sheet), starting at the unique value and including all other columns to the right (including the market value) to collect from.", "Range where Unique Value and Market Value can be found", Type:=8)
    ColNum = Application.InputBox("Type the number to determine how many columns from the Unique Value column to the Market Value column.", "Column Count from Unique Value to Market Value")
    
    ColTitleRange.Select
    ColTitleRange.Value = ColTitle
    MsgBox TableArr.Address ' The formula is not working as the TableArr only returns the cell address without the sheet name
    FormulaRange.Value = "=VLOOKUP(" & LookupVal.Address(False, False) & "," & TableArr & "," & ColNum & ",FALSE)"
End Sub

[rory]

The address property by default only returns the cell address. If you want the full address, specify the External:=True argument, or use something like:

Code: Select all

"'" & TableArr.Worksheet.Name & "'!" & TableArr.address

[Rudi]

TX. Awesome...that did the trick.
Cheers