Macro Office XP, Need a new Function

[Cyan]

I need something that goes through a document in one of exels macros (Visual Basic) that searches for the line "PNE" in collumn L and IF it's there I need it to copy or type PNE into a different cell, IF it isn't there I need it to ignore that part of the function and move on to the next.

I tried this using find, but It won't run the macro if it isn't there.

Thank you for your help

- Cyan
- Disgruntled and bored employee at the University of Utah

:D

[Cyan]

Looked like this:

Cells.Find(What:="PNE", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate
Selection.Copy
Range("P1").Select
ActiveSheet.Paste

That would be fine/perfect if I could get it to move on and not bother me about debugging the macro if PNE is not there.

[bahama llama]

I've never created a macro for this program but in general programming it would seem that a conditional would be in order here. An if, then statement would work. You may want to check out google for a tuturial on visual basic and you should be able to find something I imagine.

If columnL.PNE=False then; continue....
else ......

This obviously isn't right but the general idea should work methinks. Don.:t

[DocEvi1]

dim rowNum = 1

while(range("a" & rowNum) <> "")
if(range("l" & rowNum = "PNE") then range("x" & rowNum= "PLE")
rowNum = rowNum + 1
wend


Alternatively add some error handling to your code:


Sub Macro1()
On Error GoTo Err

Cells.Find(What:="PNE", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate

Selection.Copy

Range("P1").Select

ActiveSheet.Paste

Err:
End
End Sub


It's been a while since I have done VBA, I'm into Java now so the format of it might be wrong. Sorry.

Stefan

[Cyan]

Thanks for the help but I'm not sure how to implement the code for errors...

[DocEvi1]

it's done for you, just change the name of the macro in the post above - second quote.

Read posts :rolleyes:

Stefan

[Cyan]

Thanks Doc I was finally able to implement the code, works like a dream!

Thanks again!

[DocEvi1]

my pleasure

Stefan