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
--
Jim

First, I'd use .formulaR1C1 instead of .Formula. Excel/VBA can forgive you, but
doesn't have to.

Second, I'm not sure what column/formula returns the error or difference
indicator, but maybe you could use test like:

if application.countif(.range("x1:x" & lastrow), "yourindicator") > 0 then
'found a difference
'what should happen

You may need to add another column or change one of the formulas to return a
nice indicator that you can search for. (I told you I'm confused!)

Jim G wrote:
>
> 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
> --
> Jim

--

Dave Peterson

I'll giev the R1C1 a try, although, I didn't think it mattered since it
works-as a novice some of this confuses me too!

I have two lists Col A & Col B taken from a new data set and the other from
a fixed data set in their respective sheets. The formulas test these for
differences. since there are over a thousand rows, the result is listed in
the top rows of Col D. This can be zero or many. Col F returns the list of
unused codes.

The original lists are created from text reports and used to poulate a
template. I want to alert the user that there is a difference in the new list
and to update the template list. What do you think is the best way to
accomplish this?

--
Jim


"Dave Peterson" wrote:

> First, I'd use .formulaR1C1 instead of .Formula. Excel/VBA can forgive you, but
> doesn't have to.
>
> Second, I'm not sure what column/formula returns the error or difference
> indicator, but maybe you could use test like:
>
> if application.countif(.range("x1:x" & lastrow), "yourindicator") > 0 then
> 'found a difference
> 'what should happen
>
> You may need to add another column or change one of the formulas to return a
> nice indicator that you can search for. (I told you I'm confused!)
>
> Jim G wrote:
> >
> > 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
> > --
> > Jim
>
> --
>
> Dave Peterson
>