if cell does not contain value skip then go to next code

L

Leksv0j

New Member
Joined
Sep 15, 2017
Messages
22
Hi
Would someone be able to help with the VBA code if cell does not contain "data" the go to next code?
Eg: skip cell does not contain "Asset" the go to next code cell contains "Same"?
I have the below code which I use to generate cell range contains "asset"
ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _"=*Asset*", Operator:=xlAnd
''''' do stuff'''''''''''
i want the code above to if not found go to next code
Sub within_PO()
ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _"=*Same*", Operator:=xlAnd
Thank you so much

<tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi & welcome to the board.
Could you please supply your full code.
When posting code please click the # icon & paste your code between the tags that appear
 
Upvote 0
Fluff said:
Hi & welcome to the board.
Could you please supply your full code.
When posting code please click the # icon & paste your code between the tags that appear
Click to expand...

Sorry about that.. thanks by the way.

here is my Code:
Code: 
Columns("V:V").Select    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
        
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("My FAR Search Engine.xlsx").Activate
    Sheets("ASSET # Search").Select
    Range("B3").Select
    ActiveSheet.Paste

if criteria does not found, skip to next code for other criteria on the same range contains " *Same* and do stuff.


Thanks in advance
 
Upvote 0
Use an error handler
Code: 
Sub code1()
On Error GoTo errorhandler
ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:="=*Asset*", Operator:=xlAnd


errorhandler: Call code2
End Sub


Sub code2()
ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:="=*Same*", Operator:=xlAnd
End Sub
 
Upvote 0
@Leksv0j
I asked for you FULL code not just a small portion.
Cheers
 
Upvote 0
Hi Fluff,

here is the Full Code..

Code: 
Sub TASKSHEET()

    Columns("V:V").Select
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
        
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("My FAR Search Engine.xlsx").Activate
    Sheets("ASSET # Search").Select
    Range("B3").Select
    ActiveSheet.Paste
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    
    For Each w In Workbooks
        If Left(w.Name, 2) = "11" Then
            Workbooks(w.Name).Activate
            
        Exit For
        End If
Next w
    
    Range("Z1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC23,'[My FAR Search Engine.xlsx]ASSET # Search'!R3C2:R2000C10,COLUMNS(RC23:RC[-3]),0)"
        
    
    Selection.Copy
   
    ActiveCell.Offset(0, -3).Select
    
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1:I1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    ActiveWorkbook.BreakLink Name:= _
        "C:\Users\Desktop\My FAR Search Engine.xlsx", _
        Type:=xlExcelLinks
    Range("X1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.FormulaR1C1 = "=RC[-7]=RC[8]"
    Selection.Copy
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    Call Same
    
    MsgBox "Your Task Sheet is ready for your review..", vbOKOnly + vbInformation, "Task Sheet"
    
End Sub


Sub Same()


    Columns("V:V").Select
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
            
    
    Range("V1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ActiveCell.Offset(0, 4).Select
    ActiveCell.FormulaR1C1 = "=INDEX(C[-21],MATCH(RC[-3],C[-17],0))"
    Selection.Copy
    ActiveCell.Offset(0, -3).End(xlDown).Select
    ActiveCell.Offset(0, 3).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("V1").Select
    ActiveSheet.Range("$A$1:$AI$76").AutoFilter Field:=22
    
    
    
End Sub

Thanks Cheers
 
Upvote 0
Try
Code: 
    Columns("V:V").Select
    If WorksheetFunction.CountIf(Range("V1:V76"), "*Asset*") = 0 Then GoTo Xit
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
        
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("My FAR Search Engine.xlsx").Activate
    Sheets("ASSET # Search").Select
    Range("B3").Select
    ActiveSheet.Paste
    
    '''''''''''''''''''''''''''''''''''''''''''
    
    
    For Each w In Workbooks
        If Left(w.Name, 2) = "11" Then
            Workbooks(w.Name).Activate
            
        Exit For
        End If
Next w
    
    Range("Z1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC23,'[My FAR Search Engine.xlsx]ASSET # Search'!R3C2:R2000C10,COLUMNS(RC23:RC[-3]),0)"
        
    
    Selection.Copy
   
    ActiveCell.Offset(0, -3).Select
    
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1:I1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    ActiveWorkbook.BreakLink Name:= _
        "C:\Users\Desktop\My FAR Search Engine.xlsx", _
        Type:=xlExcelLinks
    Range("X1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.FormulaR1C1 = "=RC[-7]=RC[8]"
    Selection.Copy
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    Call Same
    
    MsgBox "Your Task Sheet is ready for your review..", vbOKOnly + vbInformation, "Task Sheet"
Xit:
    MsgBox "Asset not found"
    Call Same
End Sub


Sub Same()


    Columns("V:V").Select
    If WorksheetFunction.CountIf(Range("V1:V76"), "*Same*") = 0 Then
        MsgBox "Same not found"
        Exit Sub
    End If
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
    
    Range("V1").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ActiveCell.Offset(0, 4).Select
    ActiveCell.FormulaR1C1 = "=INDEX(C[-21],MATCH(RC[-3],C[-17],0))"
    Selection.Copy
    ActiveCell.Offset(0, -3).End(xlDown).Select
    ActiveCell.Offset(0, 3).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("V1").Select
    ActiveSheet.Range("$A$1:$AI$76").AutoFilter Field:=22
    
    
    
End Sub
 
Upvote 0
Many Thanks to you Fluff!

I guess this is what exactly what i need.

but i have my sheet filtered on A1 before running this code.

so this code will not be accurate?

Code: 
 [COLOR=#333333]If WorksheetFunction.CountIf([/COLOR][COLOR=#ff0000][B]Range("V1:V76"[/B][/COLOR][COLOR=#333333]), "*Asset*") = 0 Then GoTo Xit[/COLOR]
 
Upvote 0
So you don't want to copy all instances of Asset in V1 to V76
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
help with 'no cells found' error... (ideas on how to get around this...)
Replies
3
Views
169
Alex Blakenburg
A
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
445
Alex Blakenburg
A
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
174
bh24524
B
Q
  • Question
VBA fill right
Replies
2
Views
485
Peter_SSs
Peter_SSs
S
  • Solved
Run-time error '1004': No cells were found.
Replies
4
Views
533
sspatriots
S

Forum statistics

Threads
1,214,587
Messages
6,120,405
Members
448,958
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top