Martin sherk
Board Regular
- Joined
- Sep 11, 2022
- Messages
- 94
- Office Version
- 365
- 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:
The VBA Code:
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?
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:
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?
