Excel VBA Inputbox - range selection (type 8), but not as a fixed range, i.e. A1 is not $ A $ 1

Question

There is currently the following line for my VBA code for 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 a cell, it is automatically entered, for example. $A$1 . Can this be changed without having to manually delete $ so that Inputbox automatically picks up the cell reference as A1 ?

This is part of the VLookup automated macro that works great outside of the VLookup value, which is VLookup throughout the 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 ​​will start, for example, Cell A2, however, since we are working with dynamic lists, I will need a lookup value to change A3, A4, A5, etc. When the formula is copied over the list. In an input field using $ A $ 2, the search formula only looks at this cell link.

Answer 1

As Tim says, your problem is not related to InputBox. Rather, when you set the formula for the entire range at once, it uses FirstRow for each cell formula. Ideally, you should use .FormulaR1C1 to set the formula. This will allow you to make the formulas relative in a single pass.

In the solution below, your code has only been changed to put an unrecognized address in the first cell, and then sets the formulas in the remaining cells to equal the values ​​in the first cell. When you assign similar formulas, they make 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 

Answer 2

Your problem is not with the InputBox, but with the behavior of the Address() method.

By default, Address() without parameters returns an absolute address.

 Cells(FirstRow, myValues.Column).Address(False,False) 

will return a "non-fixed" range address.