Possible IF-Statement in VBA-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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