Excel VBA Inputbox - Select range (Type 8) but not as a fixed range i.e. A1 not $A$1
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
Your issue is not with the InputBox, but the behavior of the Address() method.
By default, Address() with no parameters returns an absolute address.
Cells(FirstRow, myValues.Column).Address(False,False)
will return the "non-fixed" range address.