Possible IF-Statement in VBA

Question

I recorded a macro that will filter my data for 34945, select all the cells with that number and then change them all to 7529 and then paste all of those into another worksheet. It looks like this:

Sub Change_34945()
'
' Change_34945 Macro
'

'
    Sheets("Transactions").Select
    ActiveSheet.Range("$A$1:$AA$31579").AutoFilter Field:=5, Criteria1:="34945"
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "7529"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Macros").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1").Select
End Sub

However, because of the way I highlighted the cells using ctrl+shift+down if there is not a row that contains 34945 excel will enter 7529 in all cells starting at the last row containing text and going to the last row in the worksheet. So, how do I get it to only enter in 7529 if there is a cell there that contains 34945? I am pretty sure I need an IF statement but unsure how to write one in VBA.

Typically after applying a Filter you'd use `SpecialCells(xlCellTypeVisible)` to reference any remaining visible rows. Eg: https://stackoverflow.com/questions/29955676/specialcellsxlcelltypevisible-error-when-autofilter-returns-zero-rows/29956062#29956062 — Tim Williams, Aug 25 '22 at 19:12
@TimWilliams Do you know where I would put that in my code here? — Trat246, Aug 25 '22 at 19:29
Start with working on modifying your code to [avoid `.select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You will hopefully start to see where you have specific shortcomings. — Cyril, Aug 25 '22 at 19:29

score 1 · Accepted Answer · answered Aug 25 '22 at 19:41

This will do what you want. Macro recorder can be useful for getting an idea of what you want, but it has many shortfalls.

Sub Change_34945()
        'filter the data by column 5 AKA E for the value 34945
    Worksheets("Transactions").Range("$A$1:$AA$31579").AutoFilter Field:=5, Criteria1:="34945"
        'replace all instances of 34945 in E with 7529
    Worksheets("Transactions").Range("$E$1:$E$31579").Replace "34945", "7529", xlWhole
        'copy all unhidden rows in the range and paste the destination
    Worksheets("Transactions").Range("$A$1:$AA$31579").SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Worksheets("Macros").Range("A1")
        'remove the autofilter delete this line if you want to keep it
    Worksheets("Transactions").Range("$A$1:$AA$31579").AutoFilter = False
End Sub

If I wanted to add another filter to a column say for example Worksheets("Transactions").Range("$A$1:$AA$31579").AutoFilter Field:=19, Criteral:="786" Could I put that after the first filter and it still work? — Trat246, Aug 25 '22 at 20:05
Yes, but it will filter down from what is already filtered. If you want filter everything then remove the auto filter and then run the new filter. — Basbadger, Aug 25 '22 at 20:20

score 0 · Answer 2 · answered Aug 25 '22 at 19:58

Something like this:

Sub Tester()
    
    Dim rng As Range, rngVis As Range, wsDest As Worksheet
    
    Set rng = Worksheets("Transactions").Range("$A$1:$AA$31579")
    
    rng.AutoFilter Field:=5, Criteria1:="34945"
    
    On Error Resume Next  'ignore error if no visible rows
    Set rngVis = rng.Columns(5).Offset(1).SpecialCells(xlCellTypeVisible) 'offset to exclude header
    On Error GoTo 0       'stop ignoring errors
    
    If Not rngVis Is Nothing Then 'if any visible cells were found
        'do the replace in ColE
        rngVis.Replace What:="34945", Replacement:="99999", LookAt:=xlWhole
        'copy the visible rows
        Set wsDest = Worksheets("Macros")
        rng.SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")
        wsDest.Range("A1").CurrentRegion.AutoFilter
    End If
    
End Sub

Possible IF-Statement in VBA

2 Answers2