Find code not working

[arlu1201]

Hi All,

I was working on this code for a friend but am not making any headway.

I have to compare the headers between 2 sheets. If sheet1 has a header which is not present in sheet2, i need to delete the column in sheet1.
In other words, ensure that the headers match between both the sheets.

I tried this code

Please Login or Register  to view this content.

When i didnt have the error handlers, i got the type mismatch error on the set scol=...line. I tried researching and checking on why the type mismatch error is occurring but couldnt.
When i put in the error handlers, even if it finds the header in both the sheets, it deletes it which should not happen.

Please help me.

[JapanDave]

Arlu try this.

Code:

Please Login or Register  to view this content.

[arlu1201]

Its still not working Dave. The search value is found in both the sheets so it should ignore it and move to the next search value (next column header).

[JapanDave]

I just realised you were trying to delete on sheet1,

This will work.

Please Login or Register  to view this content.

[arlu1201]

No, still not working. If i remove the "On error resume next" and "On error goto 0", i still get the type mismatch error on the set scol = ....line.

[JapanDave]

Ok, I tested it this time. Give this a shot.

Please Login or Register  to view this content.

Edit: Btw, if find does not find anything your will get an error which is why the error handler is needed. That is why the below code is like it is.

Please Login or Register  to view this content.

[Deepal Udeshi]

Hi,

You need to replace the line
If scol = "" Then
with
If scol Is Nothing Then

It should work then

Thanks
Deepal

[arlu1201]

Its still not working. Maybe i need to explain another thing. The first cell that is being searched contains a date ( for e.g. Nov 11 2011). THis date is present in both sheets, so it should ideally move to the next header in sheet1. But its not moving, instead it is deleting the first column.

[JapanDave]

Arlu, if you can post a dummy workbook it would be helpful. I tested the last code and it works fine. I am guessing their is slight differences in the two cells being looked up.

[arlu1201]

Here it is. Sheet 1 row 4 should be compared to sheet 2 row 2. And whatever header does not match, the entire column from sheet1 should be deleted.

[JosephP]

I reckon probably:

Please Login or Register  to view this content.

[arlu1201]

Thanks Joseph, it works great (dont know why my find code didnt work), but its not recognizing numbers. For e.g its not recognizing $5000.

[JosephP]

it does for me. did you use exactly my code or just copy some of it?

[JapanDave]

Ok see if this works for you. I have tested this and It is working for me.

Please Login or Register  to view this content.

[arlu1201]

it does for me. did you use exactly my code or just copy some of it?

I used your code completely, bit to bit.

[JapanDave]

It is not recognizing the number value due to your custom formatting. Change the format of the cell to general and see that it picks up the value.

[JosephP]

here's your file with the code in - tried and tested. can you just confirm it still deletes the 5k column for you?

[JosephP]

if it still fails, you might replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[arlu1201]

Great it works now. It didnt take the 5000 becoz of the currency formatting.

Thanks to both of you. The find code has never been a cake walk for me and this only affirms it more.

[JosephP]

can you just confirm for me that the sample I posted still didn't work for you?