Stop Macro From Falling Over

[timbo1957]

This macro works fine but falls over when it can't find a match can anyone advise how I can get around that please? I have highlighted in bold

Thanks

Please Login or Register  to view this content.

[Andrew-R]

You need to define a variable as a range, set the value of that variable to equal the results of the .Find operation (rather than trying to select the results directly) and then test for that variable being Nothing, e.g.

Please Login or Register  to view this content.

You could really do with revisiting your code - in VBA it's rarely necessary to select or activate anything, you can just tell Excel which object you're referring to - i.e.

Please Login or Register  to view this content.

[timbo1957]

Thanks Andrew-R,

A great help.

[timbo1957]

Hi Andrew-R,

You helped me out a couple of days ago and I was wondering if you could help again today?

I am using this code to delete all rows that don't have the letter "A" in column AN. How can I expand this to enable me to delete all rows that don't have the letter "A" or "n/a" in column AN.

Many thanks

Please Login or Register  to view this content.

[royUK]

Is the data in a Table format that you can Autofilter

[timbo1957]

Hi Roy,

Yes but I have never used Autoformat in a macro.

Basically what I am trying to do is -

Wkbk1 if the value in column AN is "A" or "n/a" then go to the reference number in column X.
offset that cell by one column to the left (W) and copy that value

Switch to Wkbk2 Select column A and find the reference number from Column X in Wkbk1
when the macro finds the ref no paste the copied cell offset 2 cells to the right

The macro does that okay but I have the anomaly of what to do if the ref number in wkbk1 doesn't exist in wkbk2 - I'd like it copied to the bottom of the data in row A.

Any help appreciated.

[royUK]

Try recording a macro, then it can be edited

[timbo1957]

Hi Roy,

This works down to the bit in bold where it needs adapting to allow rows containging "n/a" to remain and not be deleted.

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[timbo1957]

Hi snb,

Can you explain where I should put this in the current macro and how it works please?

Thanks

[snb]

It's a replacement of your whole macro........
If you try to understand the code, you'll see why & how it works.

[timbo1957]

Hi snb,

I have pasted it in and stepped it through but it deletes all the information I am trying to keep.

The information is in column 40 so I have made that adjustment and I want to keep all rows with either "A" or "n/a".

Please Login or Register  to view this content.

Can you advise please?

Thanks

Tim.

[royUK]

Attach a dummy workbook

[timbo1957]

Hi Roy,

I have cut out all of the info except column AN where I want to delete all rows except those with either "A" or "n/a".

Tim.