A previous poster posed a problem outlined below (and demanded a programmatic approach, despite multiple attempts to 'nudge' a formulaic approach).
I need a VBA based macro to find all matching jobs which are in Sheet1 column A, in Sheet2 column A and return the corresponding value in column B in Sheet2 to Column B, Sheet 1.
I originally solved his the query (under the disclaimer that an array approach would be more efficient) with a 2x For Each Next; Offset syntax.
I have since reapproached the issue for my own personal development via array. As it is widely held that the most efficient code touches the worksheet the least amount of times, my question is this.
How would my code below (which works) be modified to store all instances of TRUE within the
If varSource(i) = varTarget(j, 1) Then
line within the array and pass those back to the worksheet in one fell swoop.
Full code:
Option Explicit
Sub DoItArray()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngSource As Range
Dim rngTarget As Range
Dim varSource() As Variant
Dim varTarget() As Variant
Dim i As Integer
Dim j As Integer
calcMode = Application.Calculation
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
With ws1
Set rngSource = .Range("A2", .Range("A2").End(xlDown))
End With
With ws2
Set rngTarget = .Range("A2", .Range("B2").End(xlDown))
End With
varSource() = WorksheetFunction.Transpose(rngSource.Value)
varTarget() = rngTarget.Value
For i = LBound(varSource) To UBound(varSource)
For j = LBound(varTarget, 1) To UBound(varTarget, 1)
If varSource(i) = varTarget(j, 1) Then
ws1.Cells(i, 2).Value = varTarget(j, 2)
Exit For
End If
Next j
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Bookmarks