Help needed in macro/VBA selection and formatting

[hassanq23]

Hi guys,

I'm a complete excel/VBA laymen so bear with me as I try to explain my problem as best as I can. I have tried to automate a weekly report using macros/VBA and it used to work fine till now. The code (which i will paste below) is supposed to search from column J1 till that column becomes empty and delete everything that isn't the following three statements:

- Backup Completed
- Backup Completed with warnings
- Exit Status

This used to work fine but now my macro just ends up deleting everything :'(. Please help me...

Code:

Please Login or Register  to view this content.

[Bob Phillips]

Please Login or Register  to view this content.

[hassanq23]

Hi Bob,

Thanks a lot for your code! But ummmm I hit a snag. When I ran the code it gave me an error at the 'lastrow = .Range(("J1").End(xlDown).Row
statement. The error was runtime error 424, 'object required' :S

Cheers,
Hassan

[arlu1201]

Welcome to the forum, Hassanq23,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

---------- Post added at 03:32 PM ---------- Previous post was at 03:31 PM ----------

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

This will give you the correct count. If you use end(xldown) it will stop if it encounters a blank cell.

[hassanq23]

Hi,

I got the same error again after i replaced it with the following code:

Please Login or Register  to view this content.

hmmmm Will it help if i attach the excel file that I am using with this post as well?

Cheers,
Hassan

[arlu1201]

You have double (( there.

Change it to

Please Login or Register  to view this content.

[HaHoBe]

Hi, hassanq23,

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Worked for me with Excel2007.

Ciao,
Holger

[hassanq23]

Hi guys,

Made the changes as Arlette recommended and still got the same error. Then made the changes that Holger said and now get the following error:

Please Login or Register  to view this content.

Cheers,
HQ

[arlu1201]

On which line?

[hassanq23]

Hi,

The same line i.e.

Please Login or Register  to view this content.

Cheers,
HQ

[arlu1201]

It should be

Please Login or Register  to view this content.

[hassanq23]

Ok progress! its not giving me an error anymore but hmmm its not working as well. I used the debug 'step into' option on excel and saw that the code will go till the first for loop and then instead of moving on to the underlying If condition it makes a jump to the 2nd for loop and well sort of does nothing .

cheers,
Hassan

[Bob Phillips]

Where are all the double opening brackets coming from? Twice you have reproduced code that had a single bracket originally that ended up with doubles.

[hassanq23]

Hi Bob,

Ya I saw that and removed the double brackets. And now the final code looks as below, still not working properly though :

Please Login or Register  to view this content.

Cheers,
HQ

[Bob Phillips]

Can you tell us what is happening that shouldn't/not happening that should?

[hassanq23]

Hi,

Wonder if my last post went through, anyways I am trying to find out why this part of my script doesnt work. Its supposed to select the active cell in Coulmn 'J' and then skip the deleting of an entire row if the cell contains one of the three phrases below:
-Backup Completed
-Backup Completed with warnings
-Exit Status

The code is as follows:

Please Login or Register  to view this content.

Problem is that even if it detects one of the three phrases it still goes on and deletes the whole line. I'm sure I am making some incredibly stupid mistake here but please help me out.

cheers,
HQ