Euro5
asked on
VBA if not present, move on
Cells.Find(What:="FFO", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:="Envelope", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Format").Select
Range("B13").Select
ActiveSheet.Paste
I need to add to this code - if this is not found, move on.
Can anyone help? Thanks!
Euro, the code you show is from a recorded macro, correct? If you can describe in detail exactly what you want to find and what you want to do after that I'm sure the code can be improved.
Can you supply a sample workbook?
ASKER
Martin,
So the sheet will vary in format but the template will not change. I need to use the sheet data to fill out Template tab.
Certain phrases will be the same in the sheet, so I am using those on the sheet to find/select/copy/paste data to the format tab.
First phrase is FFO. So I am telling it to find that phrase, then look for the phrase Envelope that immediately follows.
Then move down one row find/select/copy/paste from column B through the end of row.
Paste that to B13 in Template tab.
Then find phrase Package on sheet and move down one row.
Starting in column B Find/select/copy/paste all data.
Paste that to B14 in Template tab.
Then find the phrase lbs. on sheet and move down to data directly below.
Starting in column B Find/select/copy/paste all data.
Paste that to B168 in Template tab.
It would continue like this.
Test.xlsx
So the sheet will vary in format but the template will not change. I need to use the sheet data to fill out Template tab.
Certain phrases will be the same in the sheet, so I am using those on the sheet to find/select/copy/paste data to the format tab.
First phrase is FFO. So I am telling it to find that phrase, then look for the phrase Envelope that immediately follows.
Then move down one row find/select/copy/paste from column B through the end of row.
Paste that to B13 in Template tab.
Then find phrase Package on sheet and move down one row.
Starting in column B Find/select/copy/paste all data.
Paste that to B14 in Template tab.
Then find the phrase lbs. on sheet and move down to data directly below.
Starting in column B Find/select/copy/paste all data.
Paste that to B168 in Template tab.
It would continue like this.
Test.xlsx
ASKER
It works fine with current code, but if it can't find the phrase, it gives me an error.
Not all of the sheets have the same information.
Not all of the sheets have the same information.
The first part of the code could be something like this.
Dim rngFound As Range
Set rngFound = Cells.Find(What:="FFO", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If Not rngFound Is Nothing Then
Set rngFound = Cells.Find(What:="Envelope", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngFound Is Nothing Then
ActiveCell.Offset(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Format").Select
Range("B13").Select
ActiveSheet.Paste
Else
MsgBox "Could not find 'Envelope'"
Exit Sub
End If
Else
MsgBox "Could not find 'FFO'"
Exit Sub
End If
ASKER
If I have multiple find/replace, could I put them between the Exit Sub & End If?
If you mean between lines 23 and 24, then no. Code placed there could never be executed since the Exit Sub (line 23) stops the execution of that Sub. You probably want to put the code after line 24,
ASKER
Ok thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Martin,
The problem is that it is not activating the cell it finds, so the wrong ranges are being copied.
For instance, the code only works if it starts from the top of the page and "finds" the first/second/third phrase in order.
Because the FFO, Envelope are in a certain order, it allows me to SELECT and FIND the right cells using offset.
Does this make sense?
Now, it is not 'moving' to the cells at all.
The problem is that it is not activating the cell it finds, so the wrong ranges are being copied.
For instance, the code only works if it starts from the top of the page and "finds" the first/second/third phrase in order.
Because the FFO, Envelope are in a certain order, it allows me to SELECT and FIND the right cells using offset.
Does this make sense?
Now, it is not 'moving' to the cells at all.
It should be doing that. For example look at line 10 in my code. That line says if you find "FFO" then execute the next line, and that line (line 11) says to select the found cell which activates it. Do you know how to debug? For help with that please see my Debugging VBA and VB6 Applications article.
dim fndrng as range
set fndrng = cels.find........
if not fndrng is nothing then
some code
end if