vba 活动单元格作为公式的输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21405521/
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
Active cell as input to formula
提问by Optimesh
I was wondering if I can use the active cell, by that I mean the cell that is "highlighted" at a given time with the square border after there was a mouse click there, as an argument in a function.
我想知道我是否可以使用活动单元格，我的意思是在给定时间“突出显示”的单元格，在那里单击鼠标后带有方形边框，作为函数中的参数。
For example, I have created a table with weighted averages with 3 weights: w1 is given in the column headers (kindly see the file below), w2 in the row headers, and w3 which complements w1 and w2 to 1.
例如，我创建了一个带有 3 个权重的加权平均值的表：w1 在列标题中给出（请参阅下面的文件），w2 在行标题中，w3 将 w1 和 w2 补充为 1。
What I'd like to do is have cells outside the table show the weights the average got when a cell in the table is selected.
我想要做的是让表格外的单元格显示选择表格中的单元格时的平均权重。
For example: Screenshot: http://imgur.com/emmBH5S/
例如：截图：http: //imgur.com/emmBH5S/
file can be found here: https://drive.google.com/file/d/0B_7-00fdslR7Tm11ODRVS296ckk/
文件可以在这里找到：https: //drive.google.com/file/d/0B_7-00fdslR7Tm11ODRVS296ckk/
Here we see the cell K12 is active, and for K12 w1=0.2, w2=0.15, and so, the cells in the weights tables above (rows 3-4) get the appropriate values according to the weights in the active cell. (of course I manually created this one for illustration purposes)
在这里，我们看到单元格 K12 处于活动状态，对于 K12，w1=0.2，w2=0.15，因此，上面权重表中的单元格（第 3-4 行）根据活动单元格中的权重获得适当的值。（当然，我手动创建了这个用于说明目的）
Is there a way to do that? Preferably without VBA if possible Couldn't find anything very useful...
有没有办法做到这一点？如果可能的话最好没有 VBA 找不到任何非常有用的东西......
Thanks in advance! A
提前致谢！一种
回答by Dick Kusleika
You don't need VBA (although you may prefer it).
您不需要 VBA（尽管您可能更喜欢它）。
W1: =INDEX($F:$AA,1,MAX(COLUMN(INDIRECT(CELL("address")))-(COLUMN(F8)-1),1))
W2: =INDEX($F:$AA,MAX(ROW(INDIRECT(CELL("address")))-(ROW(F8)-1),1),1)
W3: =J4-(G4+H4)
The CELLfunction with the addressargument returns the address for whichever cell is active. I use INDIRECTto convert that address (just a string) to a cell reference. Then I use
CELL带有address参数的函数返回活动单元格的地址。我INDIRECT用来将该地址（只是一个字符串）转换为单元格引用。然后我用
=INDEX(Range, 1, Column of Reference)
to get the w1 value - the value in the first row and the same column as the active cell. The formula doesn't care what cell you make active, so I stuck a MAXin there so it would return a zero if you're out of the range.
获取 w1 值 - 与活动单元格第一行和同一列中的值。该公式不关心您激活哪个单元格，因此我将 a 卡MAX在那里，因此如果您超出范围，它将返回零。
Note that simply selecting a cell won't trigger the change. Once you select a cell, press F9 to calculate the sheet to get the proper results.
请注意，简单地选择一个单元格不会触发更改。选择单元格后，按 F9 计算工作表以获得正确的结果。
回答by hstay
You need to use VBA. Following your example place this code in your Sheet object
您需要使用 VBA。按照您的示例将此代码放在您的 Sheet 对象中
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 8 And Target.Column > 6 Then
Range("G4").Value = Cells(8, Target.Column).Value
Range("H4").Value = Cells(Target.Row, 6).Value
Range("L4").Value = Cells(Target.Row, Target.Column).Value
End If
End Sub
Cell L4shows your selected cell value which can be used in other formulas.
单元格L4显示您选择的单元格值，可用于其他公式。
EDIT
编辑
To place your code you go to VBA window and double click on the Sheet object where you have your data. (Marked with an arrow in the picture)
Then paste yout code.
要放置您的代码，请转到 VBA 窗口并双击您拥有数据的 Sheet 对象。（图中用箭头标记）然后粘贴您的代码。
