I know just little enough about building macros in Excel with VB to be dangerous and currently have the following situation.

I've created a macro for use with log files that generally contain the same information. The macro was recorded using the Ctrl+F action to find text string 1, go to the cell containing text string 1, copy the entire contents of the cell, paste it in a new cell at the top of the page in certain cell and then move on to find text string 2, go to the cell containing text string 2, copy the entire contents... you get the picture. It works like a charm except that if it can find text strings 1, 2 and 3 but then text string 4 isn't in the spreadsheet, it stops and spits up an error.

My question: what can I add to the code to get the macro to do something along the lines of "find text string 4... if you find it, go to the cell containing text string 4, copy the entire contents of the cell, paste it in a new cell at the top of the page then move on OR if you don't find text string 4, move along to text string 5"?

Here's an example of the section of the code involving this action:

[Cells.Find(What:="total jobs created", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Range("D7").Select
ActiveSheet.Paste
Cells.Find(What:="total jobs in xml", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Range("D8").Select
ActiveSheet.Paste]

All help is greatly appreciated! Thanks in advance!

I'm not sure if this will work in your macro. Excel is closely linked to vb and in vb we use error trapping. Try the following and see. It might just work.

On Error Resume Next
'Or you can try and trap the error yourself ------->
On Error GoTo err 'Trap error at end of code------>
Cells.Find(What:="total jobs created", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Range("D7").Select
ActiveSheet.Paste
Cells.Find(What:="total jobs in xml", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Range("D8").Select
ActiveSheet.Paste

err:
'Whatever you want to do here

I entered the "On Error Resume Next" before the first Cells.Find argument in the code and it works as follows:

1. Searches for text string 1, goes to cell containing text string 1, copies contents of that cell, pastes it in cell D6
2. Searches for text string 2, goes to cell containing text string 2, copies contents of that cell, pastes it in cell D7
3. Searches for text string 3, goes to cell containing text string 3, copies contents of that cell, pastes it in cell D8
4. Searches for text string 4, goes to cell containing text string 3, copies contents of that cell, pastes it in cell E6
5. Searches for text string 5, goes to cell containing text string 3, copies contents of that cell, pastes it in cell E7
6. Searches for text string 6, goes to cell containing text string 3, copies contents of that cell, pastes it in cell E8
7. Searches for text string 7, goes to cell containing text string 7, copies contents of that cell, pastes it in cell F6
continues correctly...

So basically, this is much better than what I had before (since it doesn't STOP when it searches for text string 4 and doesn't find it) but is there a way to get it to just skip to the next search without repeating the text paste of text string 3 until it gets to text string 7? I'm fine with nothing being pasted in cells E6, E7 & E8 but it would be cool if I could get it to paste "N/A" or "No data" in those cells.

Thanks again for the help!

Sorry, I went offline for a bit.
You will probably have to code your macro to move to the next line. See in my reply the On Error GoTo err and err: code after this.
Again, I'm not used to macros, so I hope this will point you in the right direction. Maybe search on Google for copy in excel errors or the like.

Good luck.

This article has been dead for over six months. Start a new discussion instead.