作者: | 来源:互联网 | 2023-09-12 15:25
Ihaveaworkbookwithatablethatisroughly20000rowsinsizeand52columns.Attimes,Ineedt
I have a workbook with a table that is roughly 20000 rows in size and 52 columns. At times, I need to update a percentage of select rows at once. I'm hoping to use a macro to update the select cells based on a value in the row, mapped out by a second smaller table with the updated values to be entered in to table 1. Almost like a VLOOKUP function, but one that doesn't erase the cell if the entry isn't found. For example, change the Phone Number according to the Host ID.
我有一个工作簿,其表大小约为20000行,52列。有时,我需要一次更新选定行的百分比。我希望使用宏来根据行中的值更新选择单元格,由第二个较小的表格映射出来,并将更新的值输入到表1中。几乎像VLOOKUP函数,但不是如果找不到条目,则擦除单元格。例如,根据主机ID更改电话号码。
I tried to do this with an Array in the code below for a specfic set of the values in Table 1, but my values didn't update. My VBA is a bit rusty, so if someone can review and assist with getting this to function, it would be appreciated. I would like to make it update any entry in the table based on the table headers eventually.
我尝试在下面的代码中使用数组执行此操作,以获得表1中的一组特定值,但我的值未更新。我的VBA有点生疏,所以如果有人可以查看并协助使其正常运行,我们将不胜感激。我想最后根据表头更新表中的任何条目。
Sub NewNameandCostCenter()
Dim myList, myRange
Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim LastRow2 As Long
Set sht = Worksheets("NewNameMacro")
Set sht2 = Worksheets("ALL")
Set StartCell = Range("A2")
'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'set myList array
Set myList = sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
LastRow2 = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'set myRange array
Set myRange = Sheets("ALL").Range("J2:M" & LastRow2)
'Update values of cells adjacent
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 2).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 3).Value, LookAt:=xlWhole
Next cel
End Sub
Thanks, JD
谢谢,JD
1 个解决方案
1
If I understand your question correctly, you're effectively running an UPDATE query against your data, based on the values in your mapping table.
如果我正确理解了您的问题,那么您将根据映射表中的值有效地对数据运行UPDATE查询。
I've assumed the following:
我假设如下:
The "key" column is the first column in your data table and in your mapping table.
“key”列是数据表和映射表中的第一列。
The columns in your mapping table are in the same order and relative position as the columns in the data table (although this could easily be adjusted.
映射表中的列与数据表中的列具有相同的顺序和相对位置(尽管可以轻松调整。
The order of the keys in the mapping table and the data table is unsorted. If you can ensure that the keys are sorted (ideally in both sheets), then you could achieve substantially better performance with some slight modifications.
映射表和数据表中键的顺序未排序。如果您可以确保按键排序(理想情况下在两张纸中),那么只需稍作修改即可获得更好的性能。
I've hard-coded the ranges in my example, but you can reinstate the last row and last column approach if you need to.
我在我的示例中对范围进行了硬编码,但如果需要,可以恢复最后一行和最后一列方法。
I've done all of my comparisons between arrays instead of ranges, and I've done away with the Find approach. You'll find that this works, and works much more efficiently.
我已经完成了数组之间的所有比较而不是范围,并且我已经完成了Find方法。您会发现这种方法有效,并且效率更高。
Option Explicit
Sub NewNameandCostCenter()
Dim start As Double
start = Timer
Dim countOfChangedRows As Long
'set rngMap array
Dim rngMap As Range
Set rngMap = Worksheets("Map").Range("A1:D51")
'set rngData array
Dim rngData As Range
Set rngData = Worksheets("Data").Range("J2:M20001")
Dim aMap As Variant
aMap = rngMap.Value
Dim aData As Variant
aData = rngData.Value
Dim mapRow As Long
Dim datarow As Long
Dim mapcol As Long
For mapRow = LBound(aMap, 1) To UBound(aMap, 1)
For datarow = LBound(aData) To UBound(aData)
'Check the key matches in both tables
If aData(datarow, 1) = aMap(mapRow, 1) Then
countOfChangedRows = countOfChangedRows + 1
'Assumes the columns in map and data match
For mapcol = LBound(aMap, 2) + 1 To UBound(aMap, 2)
aData(datarow, mapcol) = aMap(mapRow, mapcol)
Next mapcol
End If
Next datarow
Next mapRow
rngData.Value = aData
Debug.Print countOfChangedRows & " of "; UBound(aData, 1) & " rows updated in " & Timer - start & " seconds"
End Sub
The performance is reasonable for 50 updated rows:
50个更新行的性能合理:
50 of 20000 rows updated in 0.23828125 seconds
20000行中的50行在0.23828125秒内更新
But if you need to start updating thousands of rows, then you would benefit greatly from ensuring the data is sorted and tweaking the code accordingly.
但是,如果您需要开始更新数千行,那么您将从确保数据排序和相应调整代码中受益匪浅。