vba 如何捕获单元格地址作为变量并在VB代码中使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15579952/
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
how to capture cell address as a variable and use in VB code?
提问by user75900
Need a code snippet; if some kind guru could help, please. I need to express the following cursor movement sequence in XL VBA.
需要一个代码片段；如果有好心的大师可以帮忙，请。我需要在 XL VBA 中表达以下光标移动序列。
After entering a formula in cell A1 (Col-A is otherwise empty), I need to copy the formula to all cells in the range A1:AN, where N is the last row of the table.
在单元格 A1 中输入公式后（Col-A 否则为空），我需要将公式复制到 A1:AN 范围内的所有单元格，其中 N 是表格的最后一行。
I recorded a macro to do the following (code below):
我录制了一个宏来执行以下操作（下面的代码）：
1) enter the formula (in Cell A1)
1）输入公式（在A1单元格中）
2) copy the formula
2）复制公式
3) go Right to B1
3）右转B1
4) go to the last populated cell in Col-B [using Ctrl+Down] (easiest way to find the last row)
4）转到Col-B中最后一个填充的单元格[使用Ctrl +向下]（找到最后一行的最简单方法）
5) go Left to Col-A
5) 向左走到 Col-A
6) select all cells from current to A1
6）选择从当前到A1的所有单元格
7) paste the formula to the selection
7) 将公式粘贴到选区
The part I need help with is a way to capture the cell address in step 5 as a variable so that I can use this macro on a series of files having a variable number of rows.
我需要帮助的部分是一种将步骤 5 中的单元格地址捕获为变量的方法，以便我可以在具有可变行数的一系列文件上使用此宏。
Here is the recorded macro. In this example, the last row in the table is 7952.
这是录制的宏。在此示例中，表中的最后一行是 7952。
Sub test()
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],3)"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-7951, 0).Range("A1:A7951").Select
ActiveCell.Activate
ActiveSheet.Paste
End Sub
回答by Todd
I'm not sure if your end cell is always going to be the same, meaning you may want to "un" hard code the rows, but you could try this.
我不确定您的结束单元格是否总是相同的，这意味着您可能想要“取消”对行进行硬编码，但您可以试试这个。
Sub test()
Range(Cells(1, 1), Cells(7951, 1)) = "=LEFT(RC[1],3)"
End Sub
If you are always going to put equations in column A based on the number of rows used in column B you could try this.
如果您总是要根据 B 列中使用的行数将方程式放在 A 列中，您可以试试这个。
Sub test()
' dimension the variable type
Dim lastRow As Long
' select cell "B1"
Cells(1, 2).Select
' jump to the last consecutive row in column B
Selection.End(xlDown).Select
' collect the row number into a variable
lastRow = ActiveCell.Row
' paste the equation into the variable length range
Range(Cells(1, 1), Cells(lastRow, 1)) = "=LEFT(RC[1],3)"
End Sub
回答by Todd
Kindly copy the below code to the worksheet.
请将以下代码复制到工作表中。
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Target.Address = "$A" And Target.Count = 1 And Target.HasFormula Then
Dim lastRow As Long
lastRow = Range("A65000").End(xlUp).Row
Dim rng As Range
Set rng = Range("A2:A" & lastRow)
' Target.Copy
' rng.PasteSpecial xlPasteFormulas
'OR
' rng.Formula = Target.Formula
' OR
rng.FormulaR1C1 = Target.FormulaR1C1
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
回答by user75900
Thanks Todd and user2063626,
感谢托德和 user2063626，
I decided on a simpler approach. I only needed to obtain the last row in order to set my selection area; the number of the last row is not used in the actual values to be written. The files to be manipulated are flat ascii exports; the column layout is constant, only the number of rows is variable.
我决定采用更简单的方法。我只需要获取最后一行即可设置我的选择区域；最后一行的编号不用于要写入的实际值。要操作的文件是平面ascii导出；列布局是恒定的，只有行数是可变的。
After writing the formula to A1, I move down column B and test for a value one cell at a time; if TRUE, copy the formula to the left adjacent cell; if FALSE, end process.
将公式写入 A1 后，我向下移动 B 列并一次测试一个单元格的值；如果为TRUE，则将公式复制到左侧相邻单元格；如果为 FALSE，则结束进程。
Sub FillClientCodes()
Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],3)"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(1, 0).Select
CheckCell:
ActiveCell.Activate
If ActiveCell.Value <> 0 Then
ActiveCell.Offset(0, -1).Select
ActiveCell.Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(1, 0).Select
GoTo CheckCell
Else: GoTo EndOfData
End If
EndOfData:
End Sub
It's not elegant - it runs slower than a single select and paste - but it works, and it will work on all the files I need to process. Thanks again.
它并不优雅 - 它比单个选择和粘贴运行得慢 - 但它可以工作，并且可以处理我需要处理的所有文件。再次感谢。
