Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to List-Comparison Macro to Show What Data Has Changed?
Many months ago, someone here very helpfully gave me the following macro:
Sub Update() Dim A As Integer, ML As Integer, UL As Integer ML = 2 'MasterList Start Row UL = 2 'UpdateList Start Row Do Until Sheets("Weekly Update").Cells(UL, 2) = "" Do Until Sheets("Master List").Cells(ML, 2) = "" If Sheets("Master List").Cells(ML, 2) = Sheets("Weekly Update").Cells(UL, 2) Then Worksheets("Weekly Update").Activate Sheets("Weekly Update").Range(Cells(UL, 1), Cells(UL, 14)).Copy _ Sheets("Master List").Cells(ML, 1) ML = 2 Exit Do End If ML = ML + 1 If Sheets("Master List").Cells(ML, 2) = "" Then Worksheets("Weekly Update").Activate Sheets("Weekly Update").Range(Cells(UL, 1), Cells(UL, 14)).Copy _ Sheets("Master List").Cells(ML, 1) End If Loop UL = UL + 1 ML = 2 Loop End Sub What this does is, for each row in a sheet called "Weekly Update," compare the value in Column B to the values in Column B on the sheet called "Master List." When the macro finds a match in "Master List," it copies and pastes the contents of the corresponding row (Columns A through N) from "Weekly Update" onto it. If a line item in "Weekly Update" is totally new (meaning that there is no matching value in Column B of "Master List") it adds the row to the bottom of "Master List." This all works really well, but now users have requested a couple of updates that I'm not sure how to do: 1. Instead of just using Column B in both sheets as the column for determining a match, users would prefer that the macro use the values in both Columns B and C--that is, if the macro finds a row in "Master List" where the values in B and C correspond to a row in "Weekly Update," then and only then does it overwrite the row in "Master List." If both values don't match, the macro would add the row from "Weekly Update" onto the bottom of "Master List." 2. The really tricky update is this: As it is, the macro just copies and pastes over the affected ranges in "Master List." You can easily tell which rows have been overwritten just by turning the fonts in "Weekly update to some funky, easily spotted color--that way you can scroll down the "Master List" (which can be thousands of rows long) and see what had just been brought over from the "Weekly Update." Unfortunately, there's no way to know exactly what values in the row have been updated by the macro. (For example, if the value in Column F changes from "TENTATIVE" to "FIRM", you have no way of knowing without comparing a before-and-after versions of the Master List, which is rather a pain. Is there any way for the macro to, in addition to copying-and-pasting the updating row from "Weekly Update," to actually highlight in some way (through font or cell color) all cells in the row whose values have changed? That at least would help target investigation if it were needed. It would be REALLY cool if something like Excel's "Change History" function could be replicated, something that would, in addition to updating the "Master List," generate a report of the before-and-after versions of each row affected, but probably that's a demented dream. Anyway, thanks for your patience reading this and for any help you can offer! Even if it's only to say, "none of this is possible," I'd really appreciate any perspectives you have. If it's useful, most users run this workbook on Excel 2004 for Mac, whose closest analogue in PC world seems to be Excel 2003. (So far, the macro has worked well on both platforms.) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to List-Comparison Macro to Show What Data Has Changed?
See if this works. I made improvements to speed up the coded as well as the
changes you requested. Sub Update() Dim LastRow As Long Dim NewRow As Long Dim UL As Long Dim ColB_Data Dim ColC_Data LastRow = Sheets("Master List").Range("B" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 UL = 2 'UpdateList Start Row Do Until Sheets("Weekly Update").Cells(UL, "B") = "" ColB_Data = Sheets("Weekly Update").Cells(UL, "B") ColC_Data = Sheets("Weekly Update").Cells(UL, "C") With Sheets("Master List") Found = False Set c = .Columns("B").Find(what:=Data, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then FirstAddr = c.Address Do If c.Offset(0, 1) = ColC_Data Then Worksheets("Weekly Update").Range(Cells(UL, "A"), Cells(UL, "N")).Copy _ Destination:=.Cells(c.Row, "A") .Range(Cells(c.Row, "A"), Cells(c.Row, "N")).Interior.ColorIndex = 4 Found = True Exit Do End If Set c = .Columns("B").FindNext(after:=c) Loop While Not c Is Nothing And c.Address < FirstAddr End If If Found = False Then With Worksheets("Weekly Update") .Range(Cells(UL, "A"), Cells(UL, "N")).Copy _ Destination:=Sheets("Master List").Cells(NewRow, "A") .Range(Cells(NewRow, "A"), Cells(NewRow, "N")).Interior.ColorIndex = 4 NewRow = NewRow + 1 End With End If End With UL = UL + 1 Loop End Sub "Harold Shea" wrote: Many months ago, someone here very helpfully gave me the following macro: Sub Update() Dim A As Integer, ML As Integer, UL As Integer ML = 2 'MasterList Start Row UL = 2 'UpdateList Start Row Do Until Sheets("Weekly Update").Cells(UL, 2) = "" Do Until Sheets("Master List").Cells(ML, 2) = "" If Sheets("Master List").Cells(ML, 2) = Sheets("Weekly Update").Cells(UL, 2) Then Worksheets("Weekly Update").Activate Sheets("Weekly Update").Range(Cells(UL, 1), Cells(UL, 14)).Copy _ Sheets("Master List").Cells(ML, 1) ML = 2 Exit Do End If ML = ML + 1 If Sheets("Master List").Cells(ML, 2) = "" Then Worksheets("Weekly Update").Activate Sheets("Weekly Update").Range(Cells(UL, 1), Cells(UL, 14)).Copy _ Sheets("Master List").Cells(ML, 1) End If Loop UL = UL + 1 ML = 2 Loop End Sub What this does is, for each row in a sheet called "Weekly Update," compare the value in Column B to the values in Column B on the sheet called "Master List." When the macro finds a match in "Master List," it copies and pastes the contents of the corresponding row (Columns A through N) from "Weekly Update" onto it. If a line item in "Weekly Update" is totally new (meaning that there is no matching value in Column B of "Master List") it adds the row to the bottom of "Master List." This all works really well, but now users have requested a couple of updates that I'm not sure how to do: 1. Instead of just using Column B in both sheets as the column for determining a match, users would prefer that the macro use the values in both Columns B and C--that is, if the macro finds a row in "Master List" where the values in B and C correspond to a row in "Weekly Update," then and only then does it overwrite the row in "Master List." If both values don't match, the macro would add the row from "Weekly Update" onto the bottom of "Master List." 2. The really tricky update is this: As it is, the macro just copies and pastes over the affected ranges in "Master List." You can easily tell which rows have been overwritten just by turning the fonts in "Weekly update to some funky, easily spotted color--that way you can scroll down the "Master List" (which can be thousands of rows long) and see what had just been brought over from the "Weekly Update." Unfortunately, there's no way to know exactly what values in the row have been updated by the macro. (For example, if the value in Column F changes from "TENTATIVE" to "FIRM", you have no way of knowing without comparing a before-and-after versions of the Master List, which is rather a pain. Is there any way for the macro to, in addition to copying-and-pasting the updating row from "Weekly Update," to actually highlight in some way (through font or cell color) all cells in the row whose values have changed? That at least would help target investigation if it were needed. It would be REALLY cool if something like Excel's "Change History" function could be replicated, something that would, in addition to updating the "Master List," generate a report of the before-and-after versions of each row affected, but probably that's a demented dream. Anyway, thanks for your patience reading this and for any help you can offer! Even if it's only to say, "none of this is possible," I'd really appreciate any perspectives you have. If it's useful, most users run this workbook on Excel 2004 for Mac, whose closest analogue in PC world seems to be Excel 2003. (So far, the macro has worked well on both platforms.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically update a field only when data has changed | Excel Worksheet Functions | |||
graph won't update when data is changed | Charts and Charting in Excel | |||
How do you update a pivot chart when the data is changed? | Excel Discussion (Misc queries) | |||
Charts don't update when data is changed, why not? | Charts and Charting in Excel | |||
List comparison and update of values | Excel Programming |