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

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>
 
So you don't want to copy all instances of Asset in V1 to V76

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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.


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

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

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

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

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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