5

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.

Asim
  • 105
  • 1
  • 5
  • 13
  • I don't understand what you mean by "VLookup value being fixed in the whole column." It's pretty easy to change the selected cell to a relative address, but it would be helpful to see more about how you are using it especially since you can't change the InputBox behavior the way you want. – Doug Glancy Nov 20 '12 at 13:40
  • I have updated the description to include the full code for the lookup macro – Asim Nov 20 '12 at 15:03

2 Answers2

2

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
2

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
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Cheers Doug, that works perfectly. I'll keep that rule in mind for future reference. – Asim Nov 23 '12 at 12:39