Macro iterate through with If [Closed]

Report
-
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
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

1 reply

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
215
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!