Macro iterate through with If Closed

Question

Hello,

I am trying to write a macro in excel but it has me completely stumped. I want to paste into column P the word 'resolved' for each cell where the corresponding cell in column E also has the word 'resolved'.  The number of rows of data will differ each week so I used a do until.

So far I have this (though it could be completely wrong)

    Selection.AutoFilter
    Selection.AutoFilter Field:=16, Criteria1:="Open"
    Selection.AutoFilter Field:=5, Criteria1:="Resolved"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Resolved"
    Range("R1").Select
    Selection.Copy
    Do
        If .FilterMode = True Then
        ActiveCell.Offset(1, 0).Select    
        Else
        ActiveSheet.Paste
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)
    Selection.AutoFilter Field:=16
    Selection.AutoFilter Field:=5
    Range("R1").Select
    Selection.ClearContents
End Sub

Thanks in advance for any help,

Cristina

mubashir aziz · Answer

Try below code .... Actually i'm unable to use Name funcation in VBA else it will run automatically upto the rows count in E.  I defined a name countme=counta(Sheet1!$E:$E).



Sub FillMacro()

     Range("F1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(FIND(""resolved"",LOWER(RC[-1]),1)),"""",""Resolved"")"
    Range("F1").Select
    
    'It will fill upto 1000 row and you can change it
    Selection.AutoFill Destination:=Range("F1:F1000"), Type:=xlFillDefault
    Range("F1").Select
    
    
End Sub

Macro iterate through with If

1 response

Similar discussions

Subscribe To Our Newsletter!