Results 1 to 3 of 3

Thread: vba code help

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Thanked 0 Times in 0 Posts
    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...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    For the first part:

    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.

  3. #3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts