Problem : In Excel, The work of manually searching sap code and matching the product code and then filling quantity against respective sap code in main sheet is tedious and time taking this code automates those efforts. Suppose there are 3 columns in sheet1 SAP_Code, Product_Name, and Product_Qty . Here, you have unique sap code for every produc…

searchAndswap.vba

'Problem : In Excel, The work of manually searching sap code and matching the product code and then filling quantity against respective sap code in main sheet is tedious and time taking this code automates those efforts. 
'Suppose there are 3 columns in sheet1 SAP_Code, Product_Name, and Product_Qty . Here, you have unique sap code for every product name. You have copied 2 other columns to this sheet1 Shop1_Product_Code and Shop1_Product_Qty to make a order today. 
'In general scenario, you have to manually match Shop1_Product_Code with SAP_Code and then copy paste Shop1_Product_Qty to Product_Qty for Ordering. 
'This simple vba script automate this process, also it is easily customizable for other search and swap use-cases.

'To Execute the code open a excel sheet with data, press Alt+F11 add this vba script in required sheet. Edit the row and col variables accordingly and Press Run. Wait and See the Magic!
'Updated Code with MsgBox for failed list of match cases

Sub searchAndswap()
' Edit Row_No and Col_No and Qty_Col_No according to your data

Shop1_Code_Row = 2
Shop1_Code_Col = 6
Shop1_Qty_Col = 7

Patanjali_Code_Row = 2
Patanjali_Code_Col = 1
Patanjali_Qty_Col = 3

'Do not mess with below block code
Temp = Patanjali_Code_Row
Flag = False
Failed_Code = ""

Do While Cells(Shop1_Code_Row, Shop1_Code_Col).Value <> ""

    Do While Cells(Patanjali_Code_Row, Patanjali_Code_Col).Value <> ""

        If Cells(Patanjali_Code_Row, Patanjali_Code_Col).Value = Cells(Shop1_Code_Row, Shop1_Code_Col).Value Then

            Flag = True
            Cells(Patanjali_Code_Row, Patanjali_Qty_Col).Value = Cells(Shop1_Code_Row, Shop1_Qty_Col).Value
            Exit Do
        End If
       
        Patanjali_Code_Row = Patanjali_Code_Row + 1
    Loop
   
    If Flag = False Then
        Failed_Code = Failed_Code + CStr(Cells(Shop1_Code_Row, Shop1_Code_Col).Value) + " "
    End If
   
    Flag = False
    Patanjali_Code_Row = Temp
    Shop1_Code_Row = Shop1_Code_Row + 1

Loop

Debug.Print "Completed"
MsgBox (Failed_Code)
End Sub