Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help with a macro to compare data between worksheets
Hello
I have a workbook with 8 worksheets: Menu, Raw Data, Filtered Data, Ignore, Add, Review, In Progress, Completed. I want to design a macro to compare data between some of these worksheets, but my limited programming knowledge is working against me. Each worksheet has, as its first column (i.e. column A), a unique identifier called "AGS". I want to use this field to conduct the comparison of rows between the worksheets. What I am trying to achieve, in the following order, is: Compare the data between the "Filtered Data" worksheet with that in the "Completed" worksheet. - IF THE DATA IS PRESENT, I want the macro to subtract the date in Column W of the "Completed" worksheet from today's date and if the value is greater than 365 I want the corresponding row (i.e. from the Filtered Data worksheet) copied into the "Review" worksheet. If the value is 365 or less, I want the corresponding row (i.e. from the Filtered Data worksheet) copied into the "Ignore" worksheet. - IF THE DATA IS NOT PRESENT, I then want the macro to compare the data with the "In Progress" worksheet instead. If this data IS present, then I want the corresponding row (i.e. from the Filtered Data worksheet) copied into the "Ignore" worksheet. If the data is not present, I want the corresponding row (i.e. from the Filtered Data worksheet) copied into the "Add" worksheet. Below is the code I started to work with, but now I have no idea! I think the date comparison is wrong, but dont know enough to know what else may be wrong. Sub CompareData() Sheets("Ignore").Columns("A:Z").Delete Sheets("Add").Columns("A:Z").Delete Sheets("Review").Columns("A:Z").Delete FilteredRowCount = 1 InProgressRowCount = 1 ReviewRowCount = 1 IgnoreRowCount = 1 With Sheets("Filtered Data") Do While .Range("A" & FilteredRowCount) < "" Ignore = False SearchItem = .Range("A" & FilteredRowCount) With Sheets("Completed") Set c = .Columns("A:A").Find(What:=SearchItem, _ LookIn:=xlValues) End With If c Is Nothing Then With Sheets("In Progress") Set c = .Columns("A:A").Find(What:=SearchItem, _ LookIn:=xlValues) End With If c Is Nothing Then .Rows(FilteredRowCount).Copy _ Destination:=Sheets("Add").Rows(InProgressRowCount ) InProgressRowCount = InProgressRowCount + 1 Else 'compare dates If IsDate(.Range("W" & FilteredRowCount)) = True And _ IsDate(c.Offset(0, 22)) = True Then If CDate(.Range("K" & FilteredRowCount)) 365 Then .Rows(FilteredRowCount).Copy _ Destination:=Sheets("Review").Rows(ReviewRowCount) ReviewRowCount = ReviewRowCount + 1 else Ignore = True End If else Ignore = true End If End If Else ignore = true End If if ignore = true then .Rows(FilteredRowCount).Copy _ Destination:=Sheets("Ignored").Rows(IgnoredRowCoun t) IgnoreRowCount = IgnoreRowCount + 1 end if FilteredRowCount = FilteredRowCount + 1 Loop End With MsgBox ("New data has been successfully compared to existing data.") End Sub Any help would be GREATLY appreciated. I will not be offended if you feel the need to rewrite this macro from scratch! Joe. -- If you can measure it, you can improve it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare data in two worksheets | Excel Worksheet Functions | |||
Compare data in two worksheets | Excel Worksheet Functions | |||
Compare two worksheets - macro help | Excel Discussion (Misc queries) | |||
Macro to Compare Two Worksheets? | Excel Discussion (Misc queries) | |||
compare worksheets data | Excel Discussion (Misc queries) |