Matching SAP Data Cells with Excel Cells to Select Matching Rows and Copy (Macro)

[former_member634562]

Hello all,

I am finalizing my first automated Excel-SAP macro sheet and am finding myself in great progress, but stumbled at the very end. What I am trying to do is to run the script that will open a SAP t-code, which enters a project number to open a table, which is done. Now I am trying to write a code (A loop) that will compare a whole column of data to a certain cell value in Excel in order to find the matching values to that Excel cell value, and once some data in that SAP column match that excel cell value (TRUE), then I need to get some other data from corresponding row.

sap-table.png
excel-table.png
excel-table2.png

I attached some pictures to be more specific, The cell value in Excel that says WBS element, I am looping in the WBS element in the posted SAP column to find the matching ones, and then extracting the dates from that row into the SAP cells. My For loop in the end is not very precise, and I am trying to extract the dates by the order of the Activity column, while noticing sometimes some activities are not present

here is the script and the loop in the end:

Function ProcessRow(iRow)
Dim projdef, SearchTerm, wbs, element
Dim lineitems As Long
Dim iRow1 As Long
Dim i As Long

If objSheet.Cells(4, 3) <> "" Then
    projdef = objSheet.Cells(4, 3)
Else
    projdef = "xxxxxx"
End If

objSess.FindById("wnd[0]").Maximize
objSess.FindById("wnd[0]/tbar[0]/okcd").Text = "/nzr16"
objSess.FindById("wnd[0]").sendVKey 0
objSess.FindById("wnd[0]/usr/ctxtP_PROJ-LOW").Text = projdef
objSess.FindById("wnd[0]/usr/ctxtP_PROJ-LOW").SetFocus
objSess.FindById("wnd[0]/usr/ctxtP_PROJ-LOW").caretPosition = 13
objSess.FindById("wnd[0]/usr/btn%_S_VORNR_%_APP_%-VALU_PUSH").press
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,0]").Text = "3150"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,1]").Text = "3200"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,2]").Text = "3201"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,3]").Text = "3210"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,4]").Text = "5180"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,5]").Text = "5200"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,6]").Text = "5210"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,7]").Text = "5220"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE").VerticalScrollbar.Position = 1
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE").VerticalScrollbar.Position = 2
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,6]").Text = "5230"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,7]").Text = "5300"
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,7]").SetFocus
objSess.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL_255-SLOW_I[1,7]").caretPosition = 4
objSess.FindById("wnd[1]/tbar[0]/btn[8]").press
objSess.FindById("wnd[0]/usr/ctxtP_VAR").SetFocus
objSess.FindById("wnd[0]/usr/ctxtP_VAR").caretPosition = 9
objSess.FindById("wnd[0]").sendVKey 4
objSess.FindById("wnd[1]/tbar[0]/btn[71]").press
objSess.FindById("wnd[2]/usr/txtRSYSF-STRING").Text = "KSAMC"
objSess.FindById("wnd[2]/usr/txtRSYSF-STRING").caretPosition = 5
objSess.FindById("wnd[2]").sendVKey 0
objSess.FindById("wnd[3]/usr/lbl[2,2]").SetFocus
objSess.FindById("wnd[3]/usr/lbl[2,2]").caretPosition = 1
objSess.FindById("wnd[3]").sendVKey 2
objSess.FindById("wnd[1]/tbar[0]/btn[0]").press
objSess.FindById("wnd[0]/tbar[1]/btn[8]").press
objSess.FindById("wnd[0]/usr/lbl[56,4]").SetFocus
objSess.FindById("wnd[0]/usr/lbl[56,4]").caretPosition = 2
objSess.FindById("wnd[0]").sendVKey 2
objSess.FindById("wnd[0]/usr/lbl[147,4]").SetFocus
objSess.FindById("wnd[0]/usr/lbl[147,4]").caretPosition = 1
objSess.FindById("wnd[0]").sendVKey 2
objSess.FindById("wnd[0]/usr/lbl[158,4]").SetFocus
objSess.FindById("wnd[0]/usr/lbl[158,4]").caretPosition = 2
objSess.FindById("wnd[0]").sendVKey 2
objSess.FindById("wnd[0]/usr/lbl[180,4]").SetFocus
objSess.FindById("wnd[0]/usr/lbl[180,4]").caretPosition = 9
objSess.FindById("wnd[0]").sendVKey 2


For i = 0 To 9
    
    If objSess.FindById("wnd[0]/usr/lbl[180," & 6 + i & "]").Text = wbs Then
            
        objSheet.Cells(11 + i, 8).Value = objSess.FindById("wnd[0]/usr/lbl[147," & 6 + i & "]").Text
        objSheet.Cells(11 + i, 9).Value = objSess.FindById("wnd[0]/usr/lbl[158," & 6 + i & "]").Text
        
        objSheet.Cells(11 + i, 😎 = Replace(objSheet.Cells(11 + i, 8), ".", "/")
        objSheet.Cells(11 + i, 9) = Replace(objSheet.Cells(11 + i, 9), ".", "/")
                    
    Else

        Exit For

    End If

Next i