Change Cell Value when autofiltered
 

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.

Change Cell Value when autofiltered
 

bump?
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.

Change Cell Value when autofiltered
 

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


wrote in message
oups.com...
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.