I am creating part of one tool, which merges two SAP exports into one.
I know how many account numbers might be in the export (not all of them every month) and I have it almost complete except for the find issue, when account number is not in dataset, but is found and data merged are from the last exported account number
Option Explicit
Public lcol, lrow As Long
Public tabulka As ListObject
Public ColLetA, ColLetB, kcol, ddcol, zcol, account(1 To 6), header(1 To 10) As String
Public pname, pnameSQA, ftype, wbname, strFolderName, strFolderExists, path, pathS, wbnames As String
Public pvtFld As PivotField
Public Range1, Cell1 As Range
Public quarter, q, yearfile, monthfile, y, m, mm, qp, mp, yp, fm, astrLinks, item, itemh As Variant
Public fdatum As Date
Public wb, wbp, wbco, wbs, wbSUM, wbd, wbps, wbpe As Workbook
Public ws, wsd, wsH, wsN, wsZ, wsO, wss As Worksheet
Public i, x, r, z, v As Integer
Private Sub prepaymentsSTP()
'list of relevant account numbers for STP
account(1) = "51100"
account(2) = "52100"
account(3) = "314100"
account(4) = "314200"
account(5) = "314300"
account(6) = "314400"
'list of relevant headers for STP
header(1) = "Priradenie"
header(2) = "È.dokladu"
header(3) = "PrÚs"
header(4) = "Dr.dokl."
header(5) = "Dát.dokl."
header(6) = "ÚK"
header(7) = " Èiastka vo FM"
header(8) = "FMena"
header(9) = "Text"
header(10) = "Nák.doklad"
''open workbook, activate sheet
wbnames = "Prepayments STP"
'Workbooks.Open pathS & wbnames
Set wbps = Workbooks(wbnames)
Set wss = wbps.Sheets(wbnames)
wss.Activate
Set ws = wbps.Sheets("Prepayments")
'Set ws = Sheets.Add
'ws.Name = "Prepayments"
'add headers in row 1 of new sheet
ws.Activate
Range("A1").Value = "Úèet"
Range("B1").Value = header(1)
Range("C1").Value = header(2)
Range("D1").Value = header(3)
Range("E1").Value = header(4)
Range("F1").Value = header(5)
Range("G1").Value = header(6)
Range("H1").Value = header(7)
Range("I1").Value = header(8)
Range("J1").Value = header(9)
Range("K1").Value = header(10)
'go back to STP sheet
wss.Activate
Range("A1").Select
'loop through accounts and headers to copy data from SAP export to Prepayments sheet/wb
For Each item In account
wss.Activate
Range("A1").Select
On Error Resume Next
r = Columns("E:E").Find(What:=item, LookAt:=xlWhole).Row
On Error GoTo 0
Debug.Print r
If r > 0 Then
'find header 1 to get count of data for account
Rows(r 4 & ":" & r 4).Find(What:=header(1)).Offset(2, 0).Select
Range(Selection, Selection.End(xlDown)).Select
i = Selection.Cells.Count
'copy account number i times in new sheet in first column
ws.Activate
lrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & lrow 1).Select
For v = lrow 1 To lrow i
Range("A" & v).Value = item
Next v
'declare last row for ws after submitting account number
lrow = Cells(Rows.Count, 2).End(xlUp).Row
'find header in SAP sheet and copy dataset for searched header and account
wss.Activate
For Each itemh In header
On Error Resume Next
x = Rows(r 4 & ":" & r 4).Find(What:=itemh).Offset(2, 0).Column
z = Rows(r 4 & ":" & r 4).Find(What:=itemh).Offset(2, 0).Row
Range(Cells(z, x), Cells(z i - 1, x)).Select
Selection.Copy
ws.Activate
'lcol = Cells(lrow 1, Columns.Count).End(xlToLeft).Column 'cannot use, as the first line of one column may be empty
x = Rows("1:1").Find(What:=itemh).Offset(2, 0).Column
Cells(lrow 1, x).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
On Error GoTo 0
wss.Activate
Next itemh
End If
Next item
End Sub
Problematic part of the code is
For Each item In account
wss.Activate
Range("A1").Select
On Error Resume Next
r = Columns("E:E").Find(What:=item, LookAt:=xlWhole).Row
On Error GoTo 0
Debug.Print r
If r > 0 Then
r is found for account numbers 314300 and 314400 and both are on line 168 where is 314200 located, which are not in dataset, interestingly it is not found for account number 51100, which is not in dataset either, I suppose that this might be some scholar mistake, but I am blind and cannot see it. I tried different attributes for find, but nothing works, if I use ctr f in data and try to find it manually, with no luck using different settings, such string is not here
It is string as formatting from SAP is general for these account numbers
debug.print output looks like:
x 2 102 168 168 168 168
The x is for blank space (case for debug.print for 51100)
CodePudding user response：
Problem in here
For Each item In account
wss.Activate
Range("A1").Select
On Error Resume Next
r = Columns("E:E").Find(What:=item, LookAt:=xlWhole).Row
On Error GoTo 0
Debug.Print r
If r > 0 Then
is that in the loop r is not reset! So On Error Resume Next prevents the error and leaves r with the old value (from the previous iteration of the loop)!
Solution: Initialize r for each iteration in the loop
For Each item In account
wss.Activate
Range("A1").Select
r = 0 ' Initialize r for each iteration in the loop
On Error Resume Next
r = Columns("E:E").Find(What:=item, LookAt:=xlWhole).Row
On Error GoTo 0
Debug.Print r
If r > 0 Then
You might benefit from reading
How to avoid using Select in Excel VBA.
Your code should not contain an .Select statements and you should specify a worksheet for every Range, Cells, Rows, Columns object. Otherwise it is not clear for Excel which worksheet you mean and it can fail.
Also read the manual of the Range.Find method where it says:
The settings for
LookIn,LookAt,SearchOrder, andMatchByteare saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
You only specified the parameters What:=item, LookAt:=xlWhole that means the other parametrs can be random (there is no default) to whatever the user used in the find/replace dialog before your code runs.
To make your code reliable you need to specify all of them.