VBA failed to loop my keyword range

[Martin sherk]

Hi all,

The below code is used to interact with SAP from an Excel sheet .. based on 2 conditions.
the first condition is 0 in the status column which is column H.
the 2nd condition is based on if there is one of the 4 keywords in the charge column which is column F.

Keywords are:
1) Bank Charges
2)FX Loss
3)Gain
4)Residual offset

Excel sheet:

Customer Number (Column C)	Invoice Number (column D)	Amount(Column E)	Charges (Column F)	Segment (Column G)	Status (Column H)	- (Column I)
8000180553	3433000553	36,029.00	Bank Charges		0	Pending

The VBA Code:

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nFEBA"
session.findById("wnd[0]").sendVKey 0
***Rest of the SAP code***
Dim foundKeyword As Boolean
Dim j As Integer
Dim cell As Range
Dim keywordRange As Range
Dim keywordCell As Range

Set keywordRange = Range("F10:F" & Cells(Rows.Count, 6).End(xlUp).Row)
For j = 1 To keywordRange.Cells.Count
    Set cell = keywordRange.Cells(j)

    If objSheet.Cells(iRow, 8) = "0" Then
    Debug.Print "Cell value is 0."
    If InStr(1, cell.Value, "Bank Charges", vbTextCompare) > 0 Then
    Debug.Print "Bank Charges keyword found!"
    Debug.Print "Cell value is 0."
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN").Select
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN/ssubAREA_TAB_100:FEB_BSPROC_FE:0111/cntlAREA_ACC_ASSIGNMENT/shellcont/shell").modifyCell 0, "SAKNR", "6570001"
    foundKeyword = True
    
    ElseIf InStr(1, cell.Value, "FX Loss", vbTextCompare) > 0 Then
    Debug.Print "FX Loss - F keyword found!"
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN").Select
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN/ssubAREA_TAB_100:FEB_BSPROC_FE:0111/cntlAREA_ACC_ASSIGNMENT/shellcont/shell").modifyCell 0, "SAKNR", "7960001"
    foundKeyword = True
    
    ElseIf InStr(1, cell.Value, "Gain", vbTextCompare) > 0 Then
    Debug.Print "Gain - G keyword found!"
    Debug.Print "Cell value is 0."
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN").Select
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN/ssubAREA_TAB_100:FEB_BSPROC_FE:0111/cntlAREA_ACC_ASSIGNMENT/shellcont/shell").modifyCell 0, "SAKNR", "3960001"
    foundKeyword = True
    
    ElseIf InStr(1, cell.Value, "COGS", vbTextCompare) > 0 Then
    Debug.Print "COGS - C keyword found!"
    Debug.Print "Cell value is 0."
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN").Select
    session.findById("wnd[0]/usr/tabsTAB_100/tabpACC_ASSIGN/ssubAREA_TAB_100:FEB_BSPROC_FE:0111/cntlAREA_ACC_ASSIGNMENT/shellcont/shell").modifyCell 0, "SAKNR", "4010001"
    foundKeyword = True
 
    ElseIf InStr(1, cell.Value, "Residual offset", vbTextCompare) > 0 Then
    Debug.Print "Residual partial offset keyword found!"
    Debug.Print "Cell value is 0."
    session.findById("wnd[0]/usr/tabsTAB_100/tabpALLOC/ssubAREA_TAB_100:FEB_BSPROC_FE:0106/cntlAREA_CUSTOMER_ITEMS/shellcont/shell").modifyCell 0, "DIFF_POST_TYPE", "Residual items"
    foundKeyword = True

    End If
End If
Next j

If foundKeyword Then
    Debug.Print "Keyword processed successfully."
    ' Rest of the code when a keyword is found
Else
    Debug.Print "No keyword found."
    ' Rest of the code when no keyword is found
End If


' Debug line to check if the loop completes successfully
Debug.Print "Loop completed successfully"

Problem with the above VBA:
it always displays the below message in the terminal
"No keyword found."
although I have the keyword in column F (in this example it was bank charges) why the code ignores it and doesn't iterate through my IF and elsif statement? and jump to the If not block .. what did I do wrong?

 

[Akuini]

 If objSheet.Cells(iRow, 8) = "0" Then

What are objSheet & iRow?

 

[Martin sherk]

 If objSheet.Cells(iRow, 8) = "0" Then

What are objSheet & iRow?

please find the rest of my code related to the other SAP action that working fine

Public Sub OTC()
    Dim iRow As Integer
    Dim W_BPNumber As String
    Dim W_SearchTerm As String
    Dim W_Document As String
    Dim W_Segment As String
    Dim invoiceDict As Object
    Set invoiceDict = CreateObject("Scripting.Dictionary")
    Set objSheet = ActiveWorkbook.ActiveSheet
    itemcount = 0
    itemmax = 0
    
     ' Determine the number of items to be processed: where the status is zero
    For iRow = startrow To objSheet.UsedRange.Rows.Count
        If objSheet.Cells(iRow, 8) = "0" Then
            itemmax = itemmax + 1
            
            W_SearchTerm = objSheet.Cells(iRow, 3)
            W_Document = objSheet.Cells(iRow, 4)
            
            ' Check if the customer number already exists in the dictionary
            If invoiceDict.Exists(W_SearchTerm) Then
                ' Append the new invoice to the existing customer's invoice list
                invoiceDict(W_SearchTerm) = invoiceDict(W_SearchTerm) & "," & W_Document
            Else
                ' Add the customer number and the first invoice to the dictionary
                invoiceDict.Add W_SearchTerm, W_Document
            End If
        End If
    Next iRow
    
    
   ' Update the status in cell C3
    objSheet.Cells(6, 3) = "'" & itemcount & "/" & itemmax
    
    ' Cycle through the rows with status 0 and call the ProcessRow function to process them
    For iRow = startrow To objSheet.UsedRange.Rows.Count
        If objSheet.Cells(iRow, 8) = "0" Then
            W_BPNumber = objSheet.Cells(iRow, 5)
            W_SearchTerm = objSheet.Cells(iRow, 3)
            W_Document = objSheet.Cells(iRow, 4)
            W_Segment = objSheet.Cells(iRow, 7)
            Call ProcessRow(iRow, W_BPNumber, W_SearchTerm, W_Document, W_Segment, invoiceDict)
            
            itemcount = itemcount + 1
            objSheet.Cells(6, 3) = "'" & itemcount & "/" & itemmax
        End If
    Next iRow
    
    MsgBox "Script completed.", vbInformation + vbOKOnly
End Sub

 

[Akuini]

I don't see how objSheet & iRow values (from Public Sub OTC) get to this line (in the Sub at post #1):

    If objSheet.Cells(iRow, 8) = "0" Then
    Debug.Print "Cell value is 0."

For debugging purpose, try putting a stop in this part:

    If objSheet.Cells(iRow, 8) = "0" Then
Stop
    Debug.Print "Cell value is 0."

Run the code, if the code doesn't stop at that line then it means the statement If objSheet.Cells(iRow, 8) = "0" is never true.

 

[Martin sherk]

I don't see how objSheet & iRow values (from Public Sub OTC) get to this line (in the Sub at post #1):

    If objSheet.Cells(iRow, 8) = "0" Then
    Debug.Print "Cell value is 0."

For debugging purpose, try putting a stop in this part:

    If objSheet.Cells(iRow, 8) = "0" Then
Stop
    Debug.Print "Cell value is 0."

Run the code, if the code doesn't stop at that line then it means the statement If objSheet.Cells(iRow, 8) = "0" is never true.

Hi Akuini
The code stopped at

If objSheet.Cells(iRow, 8) = "0" Then

 

[Martin sherk]

Hi Akuini
The code stopped at

However, i did put a stop at

 If InStr(1, cell.Value, "Bank Charges", vbTextCompare) > 0 Then

and the code didn't stop and completely ignored it.

 

[Akuini]

Hi Akuini
The code stopped at
If objSheet.Cells(iRow, 8) = "0" Then

Did you put the word stop below If objSheet.Cells(iRow, 8) = "0" Then as my example or you marked the line with stop sign?

 

[Martin sherk]

Did you put the word stop below If objSheet.Cells(iRow, 8) = "0" Then as my example or you marked the line with stop sign?

i did now and it completely ignored that line and code continued.

 

[Martin sherk]

@Akuini
oh now I understand! thanks so much for the heads up .. it's actually that line that made the problem, However, I fixed it but now the code loops 4 times (it enters the data in SAP 4 times), and I only have 1 Keyword so it should process the block of code under keyword bank charges (1 time) and not all of them - the other keywords (4 times).
as there any reason for that?

 

[Akuini]

i did now and it completely ignored that line and code continued.

Then it means the statement If objSheet.Cells(iRow, 8) = "0" is never true. You need to find out why.
Can you explain how objSheet & iRow values (from Public Sub OTC) get to the line (in the Sub at post #1)? What is the Sub at post #1 name?