Hi,

Please see the code below, and see if you can make sense of what I am
saying here.

Sheets("Countdown Report").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Pending Report'!C[-7],'Countdown Report'!RC[-9])"
Selection.AutoFill Destination:=Range("J2:J500")
Range("J2:J500").Select

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="0"
Range("I1").Select
ActiveCell.Offset(1, 0).Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.EntireRow.Hidden = False Then
ActiveCell.Value = "Picked/No Stock"
End If
ActiveCell.Offset(1, 0).Select
Loop

Ok, so the countif is calculating correctly, however, its the next bit
that is giving me some trouble. All i want to do is change the value of
the Status cells, which are in column I for me, to a certain value,
depending on the countif result. I have to repeat this 3 times against
different reports, and so at the end i should have different types of
entry. However, the code above changes every cell to whatever i set
ActiveCell.Value to, regardless of the filtering of the data.

Any help is greatly appreciated.

Thanks,
John.

bump?
(E-Mail Removed) wrote:
> Hi,
>
> Please see the code below, and see if you can make sense of what I am
> saying here.
>
> Sheets("Countdown Report").Select
> Range("J2").Select
> ActiveCell.FormulaR1C1 = _
> "=COUNTIF('Pending Report'!C[-7],'Countdown Report'!RC[-9])"
> Selection.AutoFill Destination:=Range("J2:J500")
> Range("J2:J500").Select
>
> Rows("1:1").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=10, Criteria1:="0"
> Range("I1").Select
> ActiveCell.Offset(1, 0).Select
> Do Until IsEmpty(ActiveCell)
> If ActiveCell.EntireRow.Hidden = False Then
> ActiveCell.Value = "Picked/No Stock"
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Ok, so the countif is calculating correctly, however, its the next bit
> that is giving me some trouble. All i want to do is change the value of
> the Status cells, which are in column I for me, to a certain value,
> depending on the countif result. I have to repeat this 3 times against
> different reports, and so at the end i should have different types of
> entry. However, the code above changes every cell to whatever i set
> ActiveCell.Value to, regardless of the filtering of the data.
>
> Any help is greatly appreciated.
>
> Thanks,
> John.

Hi

Try

Sheets("Countdown Report").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Pending Report'!C[-7],'Countdown Report'!RC[-9])"
Selection.AutoFill Destination:=Range("J2:J500")
Range("J2:J500").Select

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="0"
Range("I2").Select
for i= 2 to 500
If ActiveCell.EntireRow.Hidden = False Then
ActiveCell.Value = "Picked/No Stock"
End If
Next

--
Regards

Roger Govier


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Please see the code below, and see if you can make sense of what I am
> saying here.
>
> Sheets("Countdown Report").Select
> Range("J2").Select
> ActiveCell.FormulaR1C1 = _
> "=COUNTIF('Pending Report'!C[-7],'Countdown Report'!RC[-9])"
> Selection.AutoFill Destination:=Range("J2:J500")
> Range("J2:J500").Select
>
> Rows("1:1").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=10, Criteria1:="0"
> Range("I1").Select
> ActiveCell.Offset(1, 0).Select
> Do Until IsEmpty(ActiveCell)
> If ActiveCell.EntireRow.Hidden = False Then
> ActiveCell.Value = "Picked/No Stock"
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Ok, so the countif is calculating correctly, however, its the next bit
> that is giving me some trouble. All i want to do is change the value
> of
> the Status cells, which are in column I for me, to a certain value,
> depending on the countif result. I have to repeat this 3 times against
> different reports, and so at the end i should have different types of
> entry. However, the code above changes every cell to whatever i set
> ActiveCell.Value to, regardless of the filtering of the data.
>
> Any help is greatly appreciated.
>
> Thanks,
> John.
>