問題：將作業表 1 上的列中的值更改為與作業表 2 上列中的關聯鍵匹配的值。
表 1：
A
71.81
68.07
68.07
...
Sheet2：鍵：值對：
A(Key) B(value)
0 50
1 51.37
2 52.69
3 54.04
4 55.43
5 56.85
6 58.31
7 59.8
8 61.33
9 62.9
10 64.52
20 68.07
30 71.81
40 75
50 79.39
60 83.76
70 88.36
80 93.22
90 98.35
100 100
110 100
120 100
130 100
140 100
150 100
160 100
170 100
180 100
190 100
200 100
201 100
``
-----
expected outcome:
30
20
20
我有一些不值得一提的摔倒嘗試。
uj5u.com熱心網友回復：
替換為匹配項
- 為簡單起見，假設范圍至少有兩行資料。
Sub ReplaceWithMatches()
' 1.) Read
' Workbook
Dim wb As Workbook: Set wb = ThisWorkbook
' Source
Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
Dim srg As Range
Set srg = sws.Range("A2", sws.Cells(sws.Rows.Count, "B").End(xlUp))
Dim slrg As Range: Set slrg = srg.Columns(2) ' lookup
Dim svData() As Variant: svData = srg.Columns(1).Value ' value
' Destination
Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
Dim drg As Range
Set drg = dws.Range("A2", dws.Cells(dws.Rows.Count, "A").End(xlUp))
Dim dData() As Variant: dData = drg.Value ' lookup & value
' 2.) Modify
' Loop
Dim srIndex As Variant ' could be an error value
Dim dr As Long
For dr = 1 To UBound(dData, 1)
srIndex = Application.Match(dData(dr, 1), slrg, 0)
If IsNumeric(srIndex) Then ' match found
dData(dr, 1) = svData(srIndex, 1)
'Else ' no match found; do nothing?
End If
Next dr
' 3.) Write
' Result
drg.Value = dData
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/529973.html
標籤：擅长vba