VBA failed to loop my keyword range

M

Martin sherk

Board Regular
Joined
Sep 11, 2022
Messages
94
Office Version
  1. 365
  2. 2016
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)
8000180553343300055336,029.00Bank Charges0Pending

The VBA Code:

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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
VBA Code: 
 If objSheet.Cells(iRow, 8) = "0" Then
What are objSheet & iRow?
 
Upvote 0
Akuini said:
VBA Code: 
 If objSheet.Cells(iRow, 8) = "0" Then
What are objSheet & iRow?
Click to expand...
please find the rest of my code related to the other SAP action that working fine

VBA Code: 
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
 
Upvote 0
I don't see how objSheet & iRow values (from Public Sub OTC) get to this line (in the Sub at post #1):
VBA Code: 
    If objSheet.Cells(iRow, 8) = "0" Then
    Debug.Print "Cell value is 0."

For debugging purpose, try putting a stop in this part:
VBA Code: 
    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.
 
Upvote 0
Akuini said:
I don't see how objSheet & iRow values (from Public Sub OTC) get to this line (in the Sub at post #1):
VBA Code: 
    If objSheet.Cells(iRow, 8) = "0" Then
    Debug.Print "Cell value is 0."

For debugging purpose, try putting a stop in this part:
VBA Code: 
    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.
Click to expand...
Hi Akuini
The code stopped at
If objSheet.Cells(iRow, 8) = "0" Then
Click to expand...
 
Upvote 0
Hi Akuini
The code stopped at
If objSheet.Cells(iRow, 8) = "0" Then
Click to expand...
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?
 
Upvote 0
@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?
 
Upvote 0
Martin sherk said:
i did now and it completely ignored that line and code continued.
Click to expand...
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Help needed with VBA script Method ’Children' of object ’ISapMainWindowTarget' failed
Replies
1
Views
2K
Zaifee
Z

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top