Excel VBA 以可变列数更新表格中的单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41469605/
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 to update cell values within table with variable number of columns
提问by AlanB
I have a table of data which needs certain values to be changed based upon the value in column A. The data resides in a column headed Analysis\xx where xx is a variable value from 1 to 60. The number of columns varies per row, so that row 4 may go up to Analysis\4 whereas row 5 might go up to Analysis\30. Every value appearing in the Analysis\xx field needs to be updated, where a certain value exists in column A.
我有一个数据表，需要根据 A 列中的值更改某些值。数据驻留在标题为 Analysis\xx 的列中，其中 xx 是从 1 到 60 的变量值。每行的列数不同，这样第 4 行可能会上升到 Analysis\4 而第 5 行可能会上升到 Analysis\30。Analysis\xx 字段中出现的每个值都需要更新，其中 A 列中存在某个值。
I have code to update values based upon a single fixed column position but I'm struggling to work out how to iterate through each row to update a variable number of columns.
我有基于单个固定列位置更新值的代码，但我正在努力弄清楚如何遍历每一行以更新可变数量的列。
Any suggestions would be gratefully received.
任何建议将不胜感激。
I've attached a sample of the data below which shows a good selection of the variation, with the columns that don't need amending hidden. Row 26 actually goes all the way to column NB.
我在下面附上了一个数据样本，它显示了一个很好的变体选择，不需要修改的列隐藏了。第 26 行实际上一直到列 NB。
采纳答案by Storax
Assuming your table has a name you can use the follwoing code to loop through each row off column A and in this case just print the value of a column in the same row.
假设您的表有一个名称，您可以使用以下代码循环遍历 A 列的每一行，在这种情况下，只需打印同一行中列的值。
Sub Tester()
Dim rg As Range
Dim rgA As Range
Dim sngCell As Range
Set rg = Range("Your TableName")
Set rgA = rg.Columns(1)
For Each sngCell In rgA.Rows
Debug.Print sngCell.Offset(, 3).Value2
Next
End Sub
Does that code go in the right direction?
该代码是否朝着正确的方向发展？
EDIT: Assuming you use a listobject, this code might help you
编辑：假设您使用列表对象，此代码可能对您有所帮助
Sub ChangeTable()
Const ACCT_NO = "ABCD1234"
Const HEADING = "Analysis*"
Const NEW_VAL = "80321"
Dim tbl As ListObject
Dim x As Long
Dim i As Long
Dim hdrCount As Long
Set tbl = ActiveSheet.ListObjects("Your Name here")
hdrCount = tbl.HeaderRowRange.Columns.Count
For x = 1 To tbl.ListRows.Count
With tbl.ListRows(x)
If .Range(1, 1).Value2 = ACCT_NO Then
For i = 2 To hdrCount
If tbl.HeaderRowRange(i).Value2 Like HEADING Then
.Range(1, i).Value = NEW_VAL
End If
Next
End If
End With
Next
End Sub
回答by EKSK
Hallo have you tried to use something like this for which TEST1 is the Varibale you like to replace and Test2 is the Variable you like to chnage it to ? This code does it for the entire Worksheet.
你好，您是否尝试过使用这样的东西，其中 TEST1 是您想要替换的变量，而 Test2 是您想要将其更改为的变量？此代码为整个工作表执行此操作。
Sub Macro1()
Cells.Replace What:="TEST1", Replacement:="Test2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
