Excel VBA Inputbox - Select range (Type 8) but not as a fixed range i.e. A1 not $A$1

Question

I currently have the following line in my VBA code for an Inputbox:

Set myValues = Application.InputBox("Please select on the spreadsheet the first cell in the column with the values that you are looking for:", Type:=8)

However when I select the cell it automatically inputs e.g. $A$1. Can this be changed, without the user having to manually delete the $, so that the Inputbox would automatically pick up the cell reference as A1?

It is part of an automated VLookup macro which works perfectly aside from the VLookup value being fixed in the whole column.

Thanks in advance.

Update - Here is the full code:

Dim FirstRow As Long
Dim FinalRow As Long
Dim myValues As Range
Dim myResults As Range
Dim myCount As Integer

Sub VlookupMacroNew()  

Set myValues = Application.InputBox("Please select on the spreadsheet the first cell in the column with the values that you are looking for:", Default:=Range("A1").Address(0, 0), Type:=8)

Set myResults = Application.InputBox("Please select on the spreadsheet the first cell where you want your lookup results to start:", Type:=8)

myCount = Application.InputBox("Please enter the column number of the destination range:", Type:=1)

On Error Resume Next
myResults.EntireColumn.Insert Shift:=xlToRight
Set myResults = myResults.Offset(, -1)
FirstRow = myValues.Row
FinalRow = Cells(65536, myValues.Column).End(xlUp).Row

Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address & ", " & _
"'S:\Payroll\CONTROL SECTION\[Latest Data.xls]Sheet1'!$A$1:$B$100" & ", " & myCount & ", False)"

myValues would begin in e.g. Cell A2 however as we are working with dynamic lists I would need the lookup value to change to A3, A4, A5 etc as the formula is copied down the list. By the inputbox using $A$2 the lookup formula only looks at that cell reference.

Answer 1

As Tim says, your issue isn't with the InputBox. Rather, when you set the formula for the whole range at once, it uses FirstRow for each cell's formula. Ideally you'd use .FormulaR1C1 to set the formula. This would allow you to make the formulas relative in one pass.

The solution below just modifies your code to put a non-relative address in the first cell, and then sets the formulas in the remaining cells equal to that in the first cell. When you assign formulas like that it makes them relative:

Sub VlookupMacroNew()

Set myValues = Application.InputBox("Please select on the spreadsheet the first cell in the column with the values that you are looking for:", Default:=Range("A1").Address(0, 0), Type:=8)
Set myResults = Application.InputBox("Please select on the spreadsheet the first cell where you want your lookup results to start:", Type:=8)
myCount = Application.InputBox("Please enter the column number of the destination range:", Type:=1)

On Error Resume Next
myResults.EntireColumn.Insert Shift:=xlToRight
Set myResults = myResults.Offset(, -1)
FirstRow = myValues.Row
FinalRow = Cells(65536, myValues.Column).End(xlUp).Row

Range(myResults, myResults.Offset(FinalRow - FirstRow)).Cells(1).Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
"'S:\Payroll\CONTROL SECTION\[Latest Data.xls]Sheet1'!$A$1:$B$100" & ", " & myCount & ", False)"
Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
Range(myResults, myResults.Offset(FinalRow - FirstRow)).Cells(1).Formula
End Sub