How to compare to fields and move data?

I have 2 spread sheets in a workbook, one of which is updated as a weekly
imported report. The updated report is a copy of my master. The data on the
update can be changed/corrected and is resubmitted each week with all
previous changes included. I need to add the newest data to my master page
w/o having to review each line (1000+) I have a date field but that doesn't
relate to when change was made.
My Psuedo code looks like this:
Compare sheet1 row1 col.T to sheet 2 row 1 col.S
If not equal then copy sheet 2 row1 col.g thru n to sheet1 row 1 col h thru
o and put "new" in sheet1 row1 col.a
if T equals S then next row
do till end of file.

I have the compare working in a function but I read in another thread that
you can't move data in a function so here I am. Any help would be greatly
appreciated.

thx

There is absolutely nothing stopping you from moving data withing a function.
In VB it is however considered to be bad form. Moving data would normally be
called a side effect (which generally you don't want.) For example every
formula you use in Excel is a function. But there is no formula that allows
you to physically move data from one cell to another.

As a complete aside C programming languages don't even have Sub Procedures.
Everything is a function, so you have to use Functions to move or change data.

That having been said. I would use two range objects to handle the problem
you have. Set one Range object to Cell A1 on one sheet. Set the other Range
Object to Cell A1 on the other sheet and then move them around comparing the
results. Wherever the reustlts are not equal, Copy the entire row of the
first range object to the entire row of the second range object.

If you need help implementing that just reply back and I can get you
started...

HTH



"Brian" wrote:

> I have 2 spread sheets in a workbook, one of which is updated as a weekly
> imported report. The updated report is a copy of my master. The data on the
> update can be changed/corrected and is resubmitted each week with all
> previous changes included. I need to add the newest data to my master page
> w/o having to review each line (1000+) I have a date field but that doesn't
> relate to when change was made.
> My Psuedo code looks like this:
> Compare sheet1 row1 col.T to sheet 2 row 1 col.S
> If not equal then copy sheet 2 row1 col.g thru n to sheet1 row 1 col h thru
> o and put "new" in sheet1 row1 col.a
> if T equals S then next row
> do till end of file.
>
> I have the compare working in a function but I read in another thread that
> you can't move data in a function so here I am. Any help would be greatly
> appreciated.
>
> thx

Ok Jim, If you think it would be simpler to just move the entire row. I have
the columns locked from b-h on both sheets to prevent someone from messin up
the main data. I just thought that it might be easier to move the input data
instead of the whole row. Anyway, If you could give me a nudge on some code
references ( selecting rows adn such) I do have a little experience with c++
and I have a VB6 book for ref.
Thank for the fast reply.

"Jim Thomlinson" wrote:

> There is absolutely nothing stopping you from moving data withing a function.
> In VB it is however considered to be bad form. Moving data would normally be
> called a side effect (which generally you don't want.) For example every
> formula you use in Excel is a function. But there is no formula that allows
> you to physically move data from one cell to another.
>
> As a complete aside C programming languages don't even have Sub Procedures.
> Everything is a function, so you have to use Functions to move or change data.
>
> That having been said. I would use two range objects to handle the problem
> you have. Set one Range object to Cell A1 on one sheet. Set the other Range
> Object to Cell A1 on the other sheet and then move them around comparing the
> results. Wherever the reustlts are not equal, Copy the entire row of the
> first range object to the entire row of the second range object.
>
> If you need help implementing that just reply back and I can get you
> started...
>
> HTH
>
>
>
> "Brian" wrote:
>
> > I have 2 spread sheets in a workbook, one of which is updated as a weekly
> > imported report. The updated report is a copy of my master. The data on the
> > update can be changed/corrected and is resubmitted each week with all
> > previous changes included. I need to add the newest data to my master page
> > w/o having to review each line (1000+) I have a date field but that doesn't
> > relate to when change was made.
> > My Psuedo code looks like this:
> > Compare sheet1 row1 col.T to sheet 2 row 1 col.S
> > If not equal then copy sheet 2 row1 col.g thru n to sheet1 row 1 col h thru
> > o and put "new" in sheet1 row1 col.a
> > if T equals S then next row
> > do till end of file.
> >
> > I have the compare working in a function but I read in another thread that
> > you can't move data in a function so here I am. Any help would be greatly
> > appreciated.
> >
> > thx

Try this!!!!!!!!!!!!!

While not Sheets("Sheet1").Range("A"&i).formula <>""
if &_ strComp(Sheets("Sheet1").Range("A"&i).formula,&_
Sheets("Sheet2").Range("A"&i).formula,VbBinaryCompare)=0 then

'write the code that you need here

endif
i=i+1
Wend

The last post are wrong, Try this!!!!!!!!!!!!!

While Sheets("Sheet1").Range("A"&i).formula <>""
if &_ strComp(Sheets("Sheet1").Range("A"&i).formula,&_
Sheets("Sheet2").Range("A"&i).formula,VbBinaryCompare)=0 then

'write the code that you need here

endif
i=i+1
Wend