Comparing two columns with two conditions

[Luke101]

Hello,

I would appreciate any help on this problem. I'm trying to compare the "A" column on two sheets (Sheet1 and Upd_Dump) and if they values equal to copy over the row (A:HM) from Upd_Dump to Sheet1. I am having some trouble with code below. Any feeback would be helpful?

Luke

Please Login or Register  to view this content.

[arlu1201]

Are you getting any error? On which line?

[Luke101]

Are you getting any error? On which line?

I'm getting "Run-time error '1004': Method 'Range' of object '_Global' failed" and refers to line

Please Login or Register  to view this content.

[arlu1201]

Precede these rows

Please Login or Register  to view this content.

with Data_2.

You cannot just use Range without the corresponding sheet name.

[Luke101]

Thanks for you help but tried this below to the same error

Please Login or Register  to view this content.

[AB33]

Try this code

Please Login or Register  to view this content.

[Luke101]

Try this code

Please Login or Register  to view this content.

I can't seem to get this to work, can you explain it a little more? Especially

Please Login or Register  to view this content.

[AB33]

You are trying to compare columns A of sheet 1 with columns A of sheet ("Upd_Dump").
You can replace this line Columns(1). with Column("A:A").
I did test it on my dummy data and it worked. What the code says, look for the value of C2 (which is the cell in sheet
("Upd_Dump"). in sheet 1. If it found it, copy the value of C2 in to sheet 1.

[Luke101]

First of all, i would like to thank you for help so far. I might have initially explained the problem, how do you change the code to reflect this?

If c2 is contained on sheet1, replace that row on sheet1 with c2
if c2 is not contained on sheet copy to sheet1.

I really appreciate it!

[event21]

Hi -

Change these lines;

Please Login or Register  to view this content.

to;

Please Login or Register  to view this content.

check also the variable named "appNum_stat"

Regards,
event

[Luke101]

Thanks event for your help. Can I have gotten the macro working the way i want but it only works when i am in Sheet1. If i were in Upd-Dump Sheet it doesn't work. I know I'm probably missing a reference somewhere but I can't find it. Anyone help?

Please Login or Register  to view this content.

[arlu1201]

Generally, if i need to compare 2 sheets, i use a temp sheet where i pull in the contents of both the sheets into it and i also have a column to identify which sheet the data came from.
I sort the data in the temp sheet and do the comparisons and then copy the data wherever it needs to go.

See if this logic works for you. If you need help, i can assist.

[Luke101]

Generally, if i need to compare 2 sheets, i use a temp sheet where i pull in the contents of both the sheets into it and i also have a column to identify which sheet the data came from.
I sort the data in the temp sheet and do the comparisons and then copy the data wherever it needs to go.

See if this logic works for you. If you need help, i can assist.

Thanks for that and the logic is sound. I'm trying to get the macro working as it has to be used by others even less skilled at excel than me. I'm still to work why the macro works when I am in one worksheet and not another.

[arlu1201]

Can you upload a sample please?

[Luke101]

The problem is when i run the macro on one sheet it has a different outcome than when run the macro on a different sheet

[arlu1201]

Dont sheet1 and the other sheet have the same headers? I see that both have varying number of columns.

[Luke101]

They do have the same headers and both have the same number of columns. If they are blank in the sample it is because the data is missing but it will be used.