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

L

Leksv0j

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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
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

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs
T
  • Question Question
Range as filter criteria
Replies
5
Views
327
Peter_SSs
Peter_SSs
T
  • Question Question
Multiple filter criteria using VBA
Replies
2
Views
963
Thundercats
T
F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
298
kvsrinivasamurthy
K
F
  • Question Question
VBA: Autofilter containing specific cell value from another Worksheet
Replies
1
Views
558
Fanalis
F

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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