VBA For Loop Data Matching using Array смотреть онлайн

If I was able to help you, feel free to donate.
Paypal: [email protected]

Sub array_Match_Data()

Debug.Print Format(Now, "hh:mm:ss")

Dim rSH As Worksheet
Dim sSh As Worksheet
Set rSH = ThisWorkbook.Sheets("RAW DATA")
Set sSh = ThisWorkbook.Sheets("SEARCH DATA")

Dim rawArray() As String
Dim searchArray() As String

ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 11)
ReDim Preserve searchArray(1 To sSh.Range("A" & Rows.Count).End(xlUp).Row, 1 To 7)

For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row
For b = 1 To 11
rawArray(a, b) = rSH.Cells(a, b)
Next b
Next a

For a = 1 To sSh.Range("A" & Rows.Count).End(xlUp).Row
For b = 1 To 7
searchArray(a, b) = sSh.Cells(a, b)
Next b
Next a

Dim fName As String, lName As String
For a = 2 To UBound(searchArray)
fName = searchArray(a, 1)
lName = searchArray(a, 2)

For b = 2 To UBound(rawArray)
If rawArray(b, 1) = fName And rawArray(b, 2) = lName Then

searchArray(a, 3) = rawArray(b, 4)
searchArray(a, 4) = rawArray(b, 6)
searchArray(a, 5) = rawArray(b, 7)
searchArray(a, 6) = rawArray(b, 8)
searchArray(a, 7) = rawArray(b, 10)
Exit For
End If
Next b
Next a

'Transfer data back
For a = 2 To UBound(searchArray)
For b = 3 To 7
sSh.Cells(a, b).Value = searchArray(a, b)
Next b
Next a

Debug.Print Format(Now, "hh:mm:ss")
Debug.Print "Process Completed"

End Sub