Excel VBA 输入框 - 选择范围（类型 8）但不是固定范围，即 A1 不是 $A$1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13471809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Inputbox - Select range (Type 8) but not as a fixed range i.e. A1 not $A$1
提问by Asim
I currently have the following line in my VBAcode for an Inputbox:
我目前在我的VBA代码中有以下行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)
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 Inputboxwould automatically pick up the cell reference as A1?
但是，当我选择单元格时，它会自动输入例如$A$1. 这是否可以更改，而无需用户手动删除 $，以便Inputbox自动选择单元格引用为A1？
It is part of an automated VLookupmacro which works perfectly aside from the VLookupvalue being fixed in the whole column.
它是自动VLookup宏的一部分，除了VLookup整列中固定的值之外，它完美地工作。
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:$B0" & ", " & 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.
myValues 将从例如单元格 A2 开始，但是当我们使用动态列表时，我需要将查找值更改为 A3、A4、A5 等，因为公式被复制到列表中。通过使用 $A$2 的输入框，查找公式仅查看该单元格引用。
采纳答案by Doug Glancy
As Tim says, your issue isn't with the InputBox. Rather, when you set the formula for the whole range at once, it uses FirstRowfor each cell's formula. Ideally you'd use .FormulaR1C1to set the formula. This would allow you to make the formulas relative in one pass.
正如蒂姆所说，您的问题不在于输入框。相反，当您一次为整个范围设置公式时，它会FirstRow用于每个单元格的公式。理想情况下，您可以.FormulaR1C1用来设置公式。这将允许您在一次通过中使公式相对。
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:$B0" & ", " & myCount & ", False)"
Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
Range(myResults, myResults.Offset(FinalRow - FirstRow)).Cells(1).Formula
End Sub
回答by Tim Williams
Your issue is not with the InputBox, but the behavior of the Address()method.
您的问题不在于 InputBox，而在于Address()方法的行为。
By default, Address()with no parameters returns an absolute address.
默认情况下，Address()不带参数返回绝对地址。
Cells(FirstRow, myValues.Column).Address(False,False)
will return the "non-fixed" range address.
将返回“非固定”范围地址。
