Excel Macro Find in multiple columns

Question

I have two worksheets "Accounts" and "Mock up". In the first sheet, I have columns A to L and 1409 rows. In the second sheet, the user enters a value on cell B4. I have to search for this value in the range A1:A1409 in the first sheet. If the value is found in A100, then A100 to L100 will be assigned to cells B8:B11,B16:B19 and B22:B25 in the second sheet.

If it is not found in the range A1:A1409, then I have to search in the range E1:E1409 and return A100 to L100 again to B8:B11,B16:B19 and B22:B25 in the second sheet.

Trying to create a excel 2010 vba macro to accomplish this. Any help is appreciated.

1st Sheet

enter image description here

2nd sheet

Second Sheet

Regards, Ragav.

It would be useful to post the code you have so far, and indicate exactly where you're having problems. — Tim Williams
I agree with Tim. But you might want to check Range Object Find Method to accomplish this. Or you can try the classical loop for this as well. — L42

peege peege · Accepted Answer · 2015-01-06T01:26:42

What happens in this code:

Take the search value from "Mock Up!B4"
Search through column A on "Accounts"
If a match is found, copy the data from "Mock Up" to "Accounts"(row), matching the pictures provided.
If no match found in "A", search again through "E"
If match found on "E", copy the data from "Accounts"(row), to "Mock Up"

Code:

Sub MockUpTranfer()
Dim lastRow As Long, lRow As Long
Dim source As String, target As String, tempVal As String
Dim match As Boolean

    match = False
    source = "Mock up"
    target = "Accounts"

    'Get last Row of target Sheet and temp value to search.
    lastRow = Sheets(target).Range("A" & Rows.count).End(xlUp).row
    tempVal = Sheets(source).Range("B4")

    'Check the search value against Column A on "Accounts"
    For lRow = 1 To lastRow
        'Copy from MockUp to Accounts
        If Sheets(target).Cells(lRow, "A") = tempVal Then
            Sheets(target).Cells(lRow, "B") = Sheets(source).Range("B10")
            Sheets(target).Cells(lRow, "C") = Sheets(source).Range("B8")
            Sheets(target).Cells(lRow, "D") = Sheets(source).Range("B9")
            Sheets(target).Cells(lRow, "E") = Sheets(source).Range("B19")
            Sheets(target).Cells(lRow, "F") = Sheets(source).Range("B18")
            Sheets(target).Cells(lRow, "G") = Sheets(source).Range("B17")
            Sheets(target).Cells(lRow, "H") = Sheets(source).Range("B16")
            Sheets(target).Cells(lRow, "I") = Sheets(source).Range("B22")
            Sheets(target).Cells(lRow, "J") = Sheets(source).Range("B23")
            Sheets(target).Cells(lRow, "K") = Sheets(source).Range("B24")
            Sheets(target).Cells(lRow, "L") = Sheets(source).Range("B25")
            match = True
        End If
    Next lRow

    'No match found in "A", now searching "E"
    If match = False Then
        For lRow = 1 To lastRow
            'Copy from Accounts to MockUp
            If Sheets(target).Cells(lRow, "E") = tempVal Then
                Sheets(source).Range("B10") = Sheets(target).Cells(lRow, "B")
                Sheets(source).Range("B8") = Sheets(target).Cells(lRow, "C")
                Sheets(source).Range("B9") = Sheets(target).Cells(lRow, "D")
                Sheets(source).Range("B19") = Sheets(target).Cells(lRow, "E")
                Sheets(source).Range("B18") = Sheets(target).Cells(lRow, "F")
                Sheets(source).Range("B17") = Sheets(target).Cells(lRow, "G")
                Sheets(source).Range("B16") = Sheets(target).Cells(lRow, "H")
                Sheets(source).Range("B22") = Sheets(target).Cells(lRow, "I")
                Sheets(source).Range("B23") = Sheets(target).Cells(lRow, "J")
                Sheets(source).Range("B24") = Sheets(target).Cells(lRow, "K")
                Sheets(source).Range("B25") = Sheets(target).Cells(lRow, "L")
            End If
        Next lRow
    End If
End Sub

note: If more than one match exists, the last one will overwrite the first. This will loop through the entire Accounts Sheet.

Excel Macro Find in multiple columns

1 Answers