J
Jim G
I have a hidden sheet that compares the two lists to find additions in a new
list compared with the current list and displays the result in the first rows
of an adjacent column. Is there a way to check if differences are listed in
column D and if so, display a pop up message to alert the user to take action
in updating the master list? I want this to be part of a larger routine, so
if there are no differences then continue with the existing proceedure.
Sub CreateFormulaCols()
' will compare two lists and display the resilts at the top of the first page
'Formulas are copied to the last row with data in Col B. This should be the
exhaustive set
With Worksheets("RefList")
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("c1:C" & Lastrow).Formula =
"=IF(RC[-2]="""","""",IF(ISNUMBER(MATCH(RC[-2],C[-1],0)),"""",ROW()))"
.Range("D1" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-3]))),"""",INDEX(C[-3],MATCH(SMALL(C[-1],ROW(RC[-3])),C[-1],0)))"
.Range("E1:E" & Lastrow).Formula =
"=IF(RC[-3]="""","""",IF(ISNUMBER(MATCH(RC[-3],C[-4],0)),"""",ROW()))"
.Range("F1:F" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-5]))),"""",INDEX(C[-4],MATCH(SMALL(C[-1],ROW(RC[-5])),C[-1],0)))"
End With
End Sub
list compared with the current list and displays the result in the first rows
of an adjacent column. Is there a way to check if differences are listed in
column D and if so, display a pop up message to alert the user to take action
in updating the master list? I want this to be part of a larger routine, so
if there are no differences then continue with the existing proceedure.
Sub CreateFormulaCols()
' will compare two lists and display the resilts at the top of the first page
'Formulas are copied to the last row with data in Col B. This should be the
exhaustive set
With Worksheets("RefList")
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("c1:C" & Lastrow).Formula =
"=IF(RC[-2]="""","""",IF(ISNUMBER(MATCH(RC[-2],C[-1],0)),"""",ROW()))"
.Range("D1" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-3]))),"""",INDEX(C[-3],MATCH(SMALL(C[-1],ROW(RC[-3])),C[-1],0)))"
.Range("E1:E" & Lastrow).Formula =
"=IF(RC[-3]="""","""",IF(ISNUMBER(MATCH(RC[-3],C[-4],0)),"""",ROW()))"
.Range("F1:F" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-5]))),"""",INDEX(C[-4],MATCH(SMALL(C[-1],ROW(RC[-5])),C[-1],0)))"
End With
End Sub