我試圖將以下代碼簡化為一個回圈。我怎樣才能做到這一點？
Dim VARIANTE as LONG
For Each cell In Sheets("Libro2").Range("C1:C30000")
If cell.Value = Sheets("Libro1").Range("AA1") Then
VARIANTE = cell.Row
Sheets("Libro2").Range("Z" & VARIANTE) = 1
End If
Next
For Each cell In Sheets("Libro2").Range("C1:C30000")
If cell.Value = Sheets("Libro1").Range("AA2") Then
VARIANTE = cell.Row
Sheets("Libro2").Range("Z" & VARIANTE) = 1
End If
Next
For Each cell In Sheets("Libro2").Range("C1:C30000")
If cell.Value = Sheets("Libro1").Range("AA3") Then
VARIANTE = cell.Row
Sheets("Libro2").Range("Z" & VARIANTE) = 1
End If
Next
[...] 'and so on, and so forth
這是我嘗試過的東西，但當然沒有用。也許那里有解決方案，但由于語言問題我找不到。
For Each cell In Sheets("Libro2").Range("C1:C30000")
If cell.Value = Sheets("Libro1").Range("AA1:AA50") Then
VARIANTE = cell.Row
Sheets("Libro2").Range("Z" & VARIANTE) = 1
End If
Next
uj5u.com熱心網友回復：
請嘗試下一個代碼：
Sub testLoopCols()
Dim sh As Worksheet, shL As Worksheet, rngAA As Range, arrC, arrZ, i As Long, mtch
Set sh = ActiveSheet 'use here the sheet you need
Set shL = Sheets("Libro1")
arrC = sh.Range("C1:C30000").value 'place the range in an array for faster iteration
arrZ = sh.Range("Z1:Z30000").value 'place the range to return (using an array)
Set rngAA = shL.Range("AA1:AA50") 'the range where to match each cell of C:C range
For i = 1 To UBound(arrC)
mtch = Application.match(arrC(i, 1), rngAA, 0) 'match in a range = much faster than in an array...
If Not IsError(mtch) Then 'if a match exists:
arrZ(i, 1) = 1 'place 1 in the final array
End If
Next i
'drop the processed array result, at once:
sh.Range("Z1:Z30000").value = arrZ
MsgBox "Ready..."
End Sub
uj5u.com熱心網友回復：
這個解決方案非常出色，BigBen 僅用一句話就立即回答了我的問題，對此我深表感謝。
For Each cell In Sheets("Libro2").Range("C1:C30000")
If WorksheetFunction.CountIf(Sheets("concat").Range("AD20951:AD20956"), cell.Value) > 0 Then
VARIANTE = cell.Row
Sheets("Libro2").Range("Z" & VARIANTE) = 1
End If
Next
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/350317.html
上一篇：InventorVBA保存檔案