CODEWITHSUNDEEP
excelvba
user1492218
user1492218

Reputation: 45

Excel Macro Find in multiple columns

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.

Upvotes: 0

Views: 1655

Answers (1)

peege
peege

Reputation: 2477

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.

Upvotes: 1

Related Questions