Как сделать так, чтобы несколько операторов for эффективно выполнялись в VBA
В моем коде есть порядок поиска, и он выглядит следующим образом:
Он берет каждое значение (около 2000 диапазонов) в диапазоне A ws.sheet и ищет его на другом листе с именем диапазон A wp.sheet (около 90 диапазонов). Если конкретное значение x в диапазоне ws.sheet, например A3, не найдено в диапазоне A wp.sheet, следующим порядком поиска в листе ws.sheet является значение y в следующем диапазоне B3 (та же строка, что и значение x), которое нужно найти в лист wp.sheet во всем диапазоне B и так далее.
Это то, что делает мой цикл for, и проблема с моим кодом заключается в том, что он занимает очень много времени, поскольку сравнивает каждое значение в диапазоне ws.sheet A1-2000 со значениями в диапазоне wp.sheet A1-90. Есть ли альтернатива, которая делает это быстрее или эффективнее?
Dim wb As Workbook, wq As Object
Dim ws, wi As Worksheet, datDatum
Dim w As Long, I As Long, t As Long
Dim DefaultMsgBox()
Dim r, i As Integer
For r = 2 To 2000
Check = True:
For i = 1 To 90
If ws.Range("A" & r).Value = wp.Sheets("ABC").Range("A" & i).Value Then
wp.Sheets("ABC").Rows(i).Columns("E:AB").Copy
ws.Range("G" & r).PasteSpecial
GoTo NextR
End If
Next i
For i = 1 To 90
If ws.Range("B" & r).Value = wp.Sheets("ABC").Range("B" & i).Value Then
wp.Sheets("ABC").Rows(i).Columns("E:AB").Copy
ws.Range("G" & r).PasteSpecial
GoTo NextR
End If
Next i
For i = 1 To 90
If ws.Range("C" & r).Value = wp.Sheets("ABC").Range("C" & i).Value And ws.Range("D" & r).Value = wp.Sheets("ABC").Range("D" & i).Value Then
wp.Sheets("ABC").Rows(i).Columns("E:AB").Copy
ws.Range("G" & r).PasteSpecial
GoTo NextR
End If
Next i
NextR:
If Not Check = ws.Range("A" & r).Value = wp.Sheets("ABC").Range("A" & i).Value Or Not Check = ws.Range("B" & r).Value = wp.Sheets("ABC").Range("A" & i).Value Or Not Check = ws.Range("C" & r).Value = wp.Sheets("ABC").Range("C" & i).Value And ws.Range("D" & r).Value = wp.Sheets("ABC").Range("D" & i).Value Then
MsgBox "......"
End If
Next r
End sub
Большое спасибо за ваш комментарий. Я также добавил команду вставки. Я исключил это за ненадобностью. Порядок, в котором производится поиск, фиксирован, у него есть цель
Почему «Некст ру»? Должно быть "Следующий я"
Всё верно, я исправил
Ответы 2
Я надеюсь, вы не возражаете, если я так скажу, но ваш код трудно понять, включая ваш выбор имен переменных. Я могу порекомендовать, если вы не используете операторы .copy, закомментируйте их, и ваш код будет работать намного быстрее.
Привет Джей, спасибо за ваш комментарий. Я попытался немного упростить выбор переменных. Надеюсь, теперь стало немного понятнее.
Я бы предложил отключить ScreenUpdating и вместо этого использовать функцию поиска:
Dim cell, foundValue, lookupRange As Range
Set wp = ThisWorkbook.Sheets("ABC")
Set ws = ThisWorkbook.Sheets("WS")
r = 2
number_r = 2000
ru = 1
number_ru = 90
Application.ScreenUpdating = False
'Loop through each cell in WS, offsetting through columns A to C
For Each cell In ws.Range("A" & r & ":A" & number_r)
For i = 0 To 2
'Define range to look up in ABC
Set lookupRange = wp.Range(wp.Cells(ru, i + 1), wp.Cells(number_ru, i + 1))
'Look for current WS cell on corresponding column in ABC
Set foundValue = lookupRange.Find(cell.Offset(0, i).Value)
'If cell is found in ABC...
If Not foundValue Is Nothing Then
Select Case i
Case 2 'If found cell is in column C
Do 'Lookup loop start
'If same values on columns D...
If foundValue.Offset(0, 1).Value = cell.Offset(0, 3).Value Then
'Copy data to WS and switch to the next cell
wp.Rows(foundValue.Row).Columns("E:AB").Copy
ws.Range("G" & cell.Row).PasteSpecial
GoTo nextCell
'If not same values on columns D...
Else
'Try to find next match, if any
Set foundValue = lookupRange.FindNext(foundValue)
If foundValue Is Nothing Then GoTo noMatchFound
End If
Loop 'Repeat until WS values in column C and D match ABC values in columns C and D
Case Else 'If found cell is in column A or B
'Copy data to WS and switch to the next cell
wp.Rows(foundValue.Row).Columns("E:AB").Copy
ws.Range("G" & cell.Row).PasteSpecial
GoTo nextCell
End Select
End If
Next i
noMatchFound:
MsgBox "......" 'Message appears only when no match was found in column A, column B and column C + D
nextCell:
Next cell
Application.ScreenUpdating = True
Привет Чавес, спасибо за вашу поддержку. сразу попробую
Привет, Чавес, код не совсем то, что я искал. В вашем коде, когда значение в диапазоне (A r) находится в листе ABC range (A i), оно переходит к следующему столбцу B и продолжает поиск. Но мое намерение скорее таково: взять значение в диапазоне (A2) листа «ws» и найти его в диапазоне листа ABC (A). Если A2 находится в диапазоне A листа ABC, все в порядке. Если он не найден, переходит к B2 на листе «ws» и ищет это значение в листе ABC, диапазоне B и так далее. Это то, что утверждает мой цикл for. Однако это занимает очень много времени, так как проходит через все диапазоны. Я отредактирую свой текст дальше, чтобы сделать его более понятным
Привет, Явуз, я изменил код на основе вашего разъяснения. Я также отключил ScreenUpdating, чтобы попытаться ускорить работу кода. Дайте мне знать, если это работает так, как вы изначально ожидали.
Привет, Чавес, большое спасибо за ваши усилия. Я попробовал код с некоторыми образцами, и, похоже, он работает очень хорошо. Хотя еще одна вещь, можно ли также включить в часть «найти» дополнительный код, который после просмотра и сравнения столбцов значений A и B на обоих листах принимает не только столбец C, но принимает значения в столбце C и D вместе и сравнивает его со столбцами C и D на другом листе. И условие состоит в том, что оба значения должны быть одинаковыми на обоих листах. Я думаю о функции Application.Match. Это подойдет для него?
Здравствуйте Явуз. Извините за задержку с ответом. Забыл про условие для столбцов C+D. Сейчас добавил. Пожалуйста, дайте мне знать, если это работает, как вы ожидали, и если у вас есть какие-либо вопросы.
привет чавес, большое спасибо за ваши усилия. Тем временем я нашел решение, но ваше намного быстрее и лучше читается. Большое вам спасибо за это.
Требуется ли, чтобы значение искалось в столбце A перед столбцом B? Столбцы C и D должны проверяться вместе, или это было результатом экспериментов с комбинацией условий? Должен ли
.Copyчто-то делать или он используется с более поздней пастой?