how to handle if value NOT found VBA

[punnipah]

Hi...

i have to this VBA Code about Filter #N/A but i have a problem.When Data NOT found "#N/A" not working
-If Have "#N/A" Working
-If no Have "#N/A" not Working

i want to when no Have "#N/A" continue working

Sub Add_Subtotals()
 
 LastRow = Range("O" & Rows.Count).End(xlUp).Row - 1
Range("A1:W" & LastRow).AutoFilter Field:=23, Criteria1:="#N/A"
ActiveSheet.Range("A2:W" & LastRow).SpecialCells(xlCellTypeVisible).Copy
 
Range("A" & LastRow + 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

 

[nemmi69]

Simple approach using Goto error trap

Sub Add_Subtotals()

LastRow = Range("O" & Rows.Count).End(xlUp).Row - 1
On Error goto NoNA
Range("A1:W" & LastRow).AutoFilter Field:=23, Criteria1:="#N/A"
ActiveSheet.Range("A2:W" & LastRow).SpecialCells(xlCellTypeVisible).Copy
On Error GoTo 0
Range("A" & LastRow + 3).PasteSpecial xlPasteValues
NoNA:
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

 

[punnipah]

Simple approach using Goto error trap

Sub Add_Subtotals()

LastRow = Range("O" & Rows.Count).End(xlUp).Row - 1
On Error goto NoNA
Range("A1:W" & LastRow).AutoFilter Field:=23, Criteria1:="#N/A"
ActiveSheet.Range("A2:W" & LastRow).SpecialCells(xlCellTypeVisible).Copy
On Error GoTo 0
Range("A" & LastRow + 3).PasteSpecial xlPasteValues
NoNA:
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

Thank you Very Much ^^

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Rather than using an error to decide (there could be multiple reasons for a code line to error), I would use a more positive approach

Sub Add_Subtotals()
 
  lastrow = Range("O" & Rows.Count).End(xlUp).Row - 1
  Range("A1:W" & lastrow).AutoFilter Field:=23, Criteria1:="#N/A"
  If Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
    ActiveSheet.Range("A2:W" & lastrow).SpecialCells(xlCellTypeVisible).Copy
     
    Range("A" & lastrow + 3).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
  End If
  'Remove AutoFilter
  ActiveSheet.AutoFilterMode = False
End Sub

 

[punnipah]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Rather than using an error to decide (there could be multiple reasons for a code line to error), I would use a more positive approach

Sub Add_Subtotals()
 
  lastrow = Range("O" & Rows.Count).End(xlUp).Row - 1
  Range("A1:W" & lastrow).AutoFilter Field:=23, Criteria1:="#N/A"
  If Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
    ActiveSheet.Range("A2:W" & lastrow).SpecialCells(xlCellTypeVisible).Copy
    
    Range("A" & lastrow + 3).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
  End If
  'Remove AutoFilter
  ActiveSheet.AutoFilterMode = False
End Sub

Thank You So Much

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[punnipah]

You're welcome. Thanks for the follow-up.

@Peter_SSs

I so sorry , i have 1 more question.

IF my Data NoHave N/A i want to Value : Sheets("ReportAll").Range("A14").Value = "Data Not Found"

How to try Working





Thank you for Advance.


Sub Add_Subtotals()

lastrow = Range("O" & Rows.Count).End(xlUp).Row - 1
Range("A1:W" & lastrow).AutoFilter Field:=23, Criteria1:="#N/A"
If Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
ActiveSheet.Range("A2:W" & lastrow).SpecialCells(xlCellTypeVisible).Copy

Range("A" & lastrow + 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

 

[Peter_SSs]

Please post your code again, remembering my previous request:

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

.. explain again just what are wanting done now.