Hi

I have a list of tasks that I have extacted from a database. There are 8
columns of data. I've applied a filter to one of the columns and then I was
to put a formula in the ninth column but only in the cells included in the
filter. I then want to filter the same column on different criteria, and add
a different formula to the relevant cells in the ninth column as well. I've
worked it all out, apart from how to ensure that it will always select the
correct cell to put the formula in.

Any help would be much apprieciated.

Anita,

Let's say that your table starts in cell A1 and is continguous. To identify
the first blank cell in the the ninth column after filtering, simply use
something like:

Dim myCell As Range

For Each myCell In Range("A1").CurrentRegion. _
Columns(9).SpecialCells(xlCellTypeVisible)
If IsEmpty(myCell) Then
MsgBox "The first blank Cell is " & myCell.Address
Exit Sub
End If

HTH,
Bernie
MS Excel MVP

"AnitaML" wrote in message
...
Hi

I have a list of tasks that I have extacted from a database. There are 8
columns of data. I've applied a filter to one of the columns and then I
was
to put a formula in the ninth column but only in the cells included in the
filter. I then want to filter the same column on different criteria, and
add
a different formula to the relevant cells in the ninth column as well.
I've
worked it all out, apart from how to ensure that it will always select the
correct cell to put the formula in.

Any help would be much apprieciated.

Assuming an Autofilter

Dim rng as Range, rng1 as Range, rng2 as Range
set rng = ActiveSheet.Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
set rng1 = rng.columns(1)
On error resume next
set rng2 = rng1.Specialcells(xlVisible)
On Error goto 0
if not rng2 is nothing then
cells(rng2(1).Row,rng.columns(rng.columns.count).c olumn + 1).Select
'or cells(rng2(1).Row,9).Select ' the first is more flexible
else
msgbox "No items matched filter criteria"
End if

--
Regards,
Tom Ogilvy



"AnitaML" wrote in message
...
Hi

I have a list of tasks that I have extacted from a database. There are 8
columns of data. I've applied a filter to one of the columns and then I
was
to put a formula in the ninth column but only in the cells included in the
filter. I then want to filter the same column on different criteria, and
add
a different formula to the relevant cells in the ninth column as well.
I've
worked it all out, apart from how to ensure that it will always select the
correct cell to put the formula in.

Any help would be much apprieciated.