vba code help

Topic

New Reply

Code:

Sub CreateErrorReports()
	
Dim v1 As Workbook
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet, w4 As Worksheet, w5 As Worksheet
Dim x1 As Long, x2 As Long, x3 As Long, x4 As Long
Dim y1 As Range, y2 As Range, y3 As Range, y4 As Range
Dim Msg As String, pw As String

	Set v1 = ThisWorkbook
	Set w1 = v1.Worksheets("Stats")
	Set w2 = v1.Worksheets("Daily Files")
	Set w3 = v1.Worksheets("Payment Files")
	Set w4 = v1.Worksheets("Processed but not paid for")
	Set w5 = v1.Worksheets("Paid for but not processed")

	x2 = w2.Range("D" & Rows.Count).End(xlUp).Row		   'Daily Files
	x3 = w3.Range("A" & Rows.Count).End(xlUp).Row		   'Payment Files
	x4 = w4.Range("D" & Rows.Count).End(xlUp).Row + 1	   'Processed but not paid for
	x5 = w5.Range("A" & Rows.Count).End(xlUp).Row + 1	   'Paid for but not processed
	
	Set y2 = w2.Range("D2:D" & x2)  'Daily files range - If not in y3, copy row to x4 (Col A)
	Set y3 = w3.Range("A2:A" & x3)  'Payment files range - If not in y2, copy row to x5 (Col A)

End Sub

I need to check each cell (text) in y2 exists in y3, if not, copy row from y2 to x4 (Col A). Then check each cell in y3 exists in y2, if not copy row from y3 to x5 (Col A). Help please…

Reply | Quote

Replies

For the first part:

Code:

Dim oCell As Range
' Loop through the cells in y2
For Each oCell In y2
  ' Look for cell value in y3
  If y3.Find(What:=oCell.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
	' If not found, copy row to w4
	oCell.EntireRow.Copy Destination:=w4.Range("A" & x4)
	' Increase x4 for next time
	x4 = x4 + 1
  End If
Next oCell

You should be able to work out the other one yourself now.

Reply | Quote

Thankyou very much!

Reply | Quote