VBA filters data from two different sheets based on the document number

Public sub filter ()
'Sheet1 is the total table exported from Qiankun.
'Sheet2 is the ticket No. maintained by yourself.
'This function is used to automatically filter the matching ticket numbers. If the ticket numbers that have not been invoiced are in sheet1, the value of the S column of sheet1 is 1; otherwise, it is 0.
'Just copy the invoiced ticket number to the E column of sheet2. The format of sheet1 exported from Qian Kun remains unchanged.

 

'If workbooks (1). worksheets ("sheet1") is nothing then
'Msgbox "sheet1 does not exist"
'Else
'Msgbox "sheet1 exists"
'End if




Dim sheet1rowcount as integer
Dim sheet2rowcount as integer
Sheet1rowcount = sheet1.usedrange. Rows. Count 'qiankun exported Excel
Sheet2rowcount = sheet2.usedrange. Rows. Count 'Self-maintained Excel
Dim sourcedata as string
Dim targetdata as string
Dim isfound as string
Isfound = "no" 'does not match

Dim I as integer
Number of inner cycles of dim J as integer
Dim K as integer
Dim P as integer



'I don't know why all spaces cannot be deleted in one loop.
For p = 1 to 20
Sheet2rowcount = sheet2.usedrange. Rows. Count 'Self-maintained Excel
For k = 1 to sheet2rowcount
If rtrim (ltrim (sheet2.cells (K, 5). Value) = "" then
Sheet2.rows (k). Delete
End if
Next
Next

Sheet2rowcount = sheet2.usedrange. Rows. Count 'Self-maintained Excel

For I = 4 to sheet1rowcount 'qiankun exported Excel
Sourcedata = sheet1.cells (I, 5). Value
For j = 1 to sheet2rowcount
Targetdata = sheet2.cells (J, 5). value' self-maintained Excel
If sourcedata = targetdata then
Sheet1.cells (I, 19). value = 1' matched
Isfound = "yes"
Else
Sheet1.cells (I, 19). value = 0' match failed
Isfound = "no"
End if
If isfound = "yes" then
Exit
End if
Next
Next


'Copy to sheet3, sheet4 after successful matching
Dim R as integer
Dim s as integer
R = 0
S = 0


For I = 4 to sheet1rowcount 'qiankun exported Excel
If sheet1.cells (I, 19) = 1 then
R = R + 1
Sheet1.rows (I). Copy sheet3.rows (r)
Else
S = S + 1
Sheet1.rows (I). Copy sheet4.rows (s)
End if
Next

 

VBA filters data from two different sheets based on the document number