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]
 
Sort by date Sort by votes
Fluff said:
So you don't want to copy all instances of Asset in V1 to V76
Click to expand...

Yes please..

My sheet is filtered in A1 by Color. (automatic and RED) so only the RED is what i have to work on.

whatever Asset found in Column ("Z:Z") (filtered on) is what i only want to copy.

Thank you again Fluff.
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Leksv0j said:
Yes please..

My sheet is filtered in A1 by Color. (automatic and RED) so only the RED is what i have to work on.

whatever Asset found in Column ("Z:Z") (filtered on) is what i only want to copy.

Thank you again Fluff.
Click to expand...


Columns ("V:V") rather..
 
Upvote 0
Ok Try
Code: 
Sub TASKSHEET()
Columns("V:V").Select
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
    If Range("V1:V76").SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit
    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
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
    If Range("V1:V76").SpecialCells(xlVisible).Cells.Count = 1 Then
        MsgBox "Same not found"
        Exit Sub
    End If

    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
Fluff said:
Ok Try
Code: 
Sub TASKSHEET()
Columns("V:V").Select
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
    If Range("V1:V76").SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit
    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
    ActiveSheet.Range("$A$1:$AA$76").AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
    If Range("V1:V76").SpecialCells(xlVisible).Cells.Count = 1 Then
        MsgBox "Same not found"
        Exit Sub
    End If

    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
Click to expand...

It Works PERFECTLY!... Thank you very much Fluff!
I really appreciate your help...
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
hi there Fluff!,

Good day to you. i am having quite some issues on our code below in the range selection. Task sheet is not fixed on the range selection. some of them are higher than that.
tried to adjust it but no luck. some times even no asset found it skips the GO TO Xit and continue the code.

Code: 
[COLOR=#333333][I]Columns("V:V").Select[/I][/COLOR]    ActiveSheet.Range([B][COLOR=#ff0000]"$A$1:$AA$76"[/COLOR][/B]).AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd 
    If Range([B][COLOR=#ff0000]"V1:V76"[/COLOR][/B]).SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit[COLOR=#222222][FONT=Verdana]

thanx mate.[/FONT][/COLOR]
 
Upvote 0
This should allow for more than 76 rows.
Code: 
Sub TASKSHEET()

    Dim Usdrws As Long

    Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Columns("V:V").Select
    ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
    If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit
    [COLOR=#ff0000]Range("W1").Select[/COLOR]
    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(Usdrws)
    
    MsgBox "Your Task Sheet is ready for your review..", vbOKOnly + vbInformation, "Task Sheet"
Xit:
    MsgBox "Asset not found"
    Call Same(Usdrws)
End Sub


Sub Same(Usdrws As Long)


    Columns("V:V").Select
    ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
    If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then
        MsgBox "Same not found"
        Exit Sub
    End If

    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
As for skipping the goto, if you step through the code line by line (by pressing F8), when the line in red above is highlighted, how many visible rows are there?
 
Upvote 0
Fluff said:
This should allow for more than 76 rows.
Code: 
Sub TASKSHEET()

    Dim Usdrws As Long

    Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Columns("V:V").Select
    ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd
    If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit
    [COLOR=#ff0000]Range("W1").Select[/COLOR]
    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(Usdrws)
    
    MsgBox "Your Task Sheet is ready for your review..", vbOKOnly + vbInformation, "Task Sheet"
Xit:
    MsgBox "Asset not found"
    Call Same(Usdrws)
End Sub


Sub Same(Usdrws As Long)


    Columns("V:V").Select
    ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
        "=*Same*", Operator:=xlAnd
    If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then
        MsgBox "Same not found"
        Exit Sub
    End If

    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
As for skipping the goto, if you step through the code line by line (by pressing F8), when the line in red above is highlighted, how many visible rows are there?
Click to expand...

Code: 
[FONT=Verdana]      Dim Usdrws As Long
[/FONT]
    Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Columns("V:V").Select
    ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
        "=*Asset*", Operator:=xlAnd 
    If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit[COLOR=#222222][FONT=Verdana]

tried above code with no asset on the range but still continues to Range "W1". There is no rows visible only V1 as header.
[/FONT][/COLOR]
 
Upvote 0
Don't understand that.
In the intermediate window (Usually below the code window, Ctrl+g will make it appear, if not already visible) put this
Code: 
?activesheet.Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Making sure your data sheet is visible & hit enter
It should give you a number, is that number the same as the last visible row of data?
 
Upvote 0
Fluff said:
Don't understand that.
In the intermediate window (Usually below the code window, Ctrl+g will make it appear, if not already visible) put this
Code: 
?activesheet.Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Making sure your data sheet is visible & hit enter
It should give you a number, is that number the same as the last visible row of data?
Click to expand...

sorry about that.

what i meant was, as my sheet is filtered in A1 with color RED, i tried column Columns("V:V") with no " Asset" to be found on the range, i stepped through the code but still it continues to step into the next line selecting Range("W1").Select. where as it should go to msgbox "NO Asset Found". i have now 1000 rows.
 
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
277
Peter_SSs
Peter_SSs
T
  • Question Question
Range as filter criteria
Replies
5
Views
265
Peter_SSs
Peter_SSs
T
  • Question Question
Multiple filter criteria using VBA
Replies
2
Views
824
Thundercats
T
F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
291
kvsrinivasamurthy
K
F
  • Question Question
VBA: Autofilter containing specific cell value from another Worksheet
Replies
1
Views
538
Fanalis
F

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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