Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

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!
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Generally it is like

dim fndrng as range
set fndrng = cels.find........
if not fndrng is nothing then
   some code
end if
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?
Avatar of Euro5

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
Avatar of Euro5

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.
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

Open in new window

Avatar of Euro5

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,
Avatar of Euro5

ASKER

Ok thanks!
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Euro5

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.
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.