Selecting first cell in a colmn after a filter has bee applied
 

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.

Selecting first cell in a colmn after a filter has bee applied
 

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.

Selecting first cell in a colmn after a filter has bee applied
 

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.

Selecting first cell in a colmn after a filter has bee applied
 

Anita,

I made a copy and paste error, and left off the last line:

Next myCell

Sorry about that,
Bernie
MS Excel MVP


"AnitaML" wrote in message
...
Hi Bernie

I gave this one a try, but I get an error VB wants me to use 'Next' if i
use
'With'

"Bernie Deitrick" wrote:

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.

Selecting first cell in a colmn after a filter has bee applied
 

Anita,

Your code seems a little confused, so I'm going to go out on a limb and ask
that you try this code below. I think it does what you want - or at least
the first part of what you want.

HTH,
Bernie
MS Excel MVP


Sub BacklogFormula2()

Dim myRange As Range
Dim mySht As Worksheet

On Error Resume Next

Set mySht = Sheets("Build PE Config")
Set myRange = mySht.Range("A1").CurrentRegion

If Not mySht.AutoFilterMode Then myRange.AutoFilter
myRange.AutoFilter Field:=3, Criteria1:="Ready"
Intersect(mySht.Range("I:I"), myRange). _
SpecialCells(xlCellTypeVisible). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"

myRange.AutoFilter
End Sub





"AnitaML" wrote in message
...
HI Bernie

When I add this code to my macro, it fills the first cell, then gives me a
message to tell me what the first cell is, but it exits out and does not
run
the rest of my macro and it doesn't fill all the other cells in the row
related to filter I've applied.

Below is the Macro as I have it so farThis works quite well, only the
first
loop doesn't stop and it eventually comes back with an error (after it has
gone down the whole column!)

Sub BacklogFormula()


Sheets("Build PE Config").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Ready"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With

Selection.AutoFilter Field:=3, Criteria1:="Completed"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(R[-25]C[-1]<=0.33,""On
time"",""Missed"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
Selection.FillDown

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With


End Sub

Thanks for your help so far, I'm learning all the time


"Bernie Deitrick" wrote:

Anita,

I made a copy and paste error, and left off the last line:

Next myCell

Sorry about that,
Bernie
MS Excel MVP


"AnitaML" wrote in message
...
Hi Bernie

I gave this one a try, but I get an error VB wants me to use 'Next' if
i
use
'With'

"Bernie Deitrick" wrote:

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.

Selecting first cell in a colmn after a filter has bee applied
 

Hi Bernie

Yep that would be me - confused always

Anway, I tried you code and it seems to get as far a applying the filter,
and finding the first correct cell, but it doesn't insert the formula into
the relevant cells in column I. Also, I have about 35 tabs in this
spreadsheet and if it is on anther tab when I run the macro, it doesn't do
anything at all. Once I have this macro working how I want, I want to be able
to repeat it for most of the other tabs.

Thanks for you help.

Anita

Anita

"Bernie Deitrick" wrote:

Anita,

Your code seems a little confused, so I'm going to go out on a limb and ask
that you try this code below. I think it does what you want - or at least
the first part of what you want.

HTH,
Bernie
MS Excel MVP


Sub BacklogFormula2()

Dim myRange As Range
Dim mySht As Worksheet

On Error Resume Next

Set mySht = Sheets("Build PE Config")
Set myRange = mySht.Range("A1").CurrentRegion

If Not mySht.AutoFilterMode Then myRange.AutoFilter
myRange.AutoFilter Field:=3, Criteria1:="Ready"
Intersect(mySht.Range("I:I"), myRange). _
SpecialCells(xlCellTypeVisible). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"

myRange.AutoFilter
End Sub





"AnitaML" wrote in message
...
HI Bernie

When I add this code to my macro, it fills the first cell, then gives me a
message to tell me what the first cell is, but it exits out and does not
run
the rest of my macro and it doesn't fill all the other cells in the row
related to filter I've applied.

Below is the Macro as I have it so farThis works quite well, only the
first
loop doesn't stop and it eventually comes back with an error (after it has
gone down the whole column!)

Sub BacklogFormula()


Sheets("Build PE Config").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Ready"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With

Selection.AutoFilter Field:=3, Criteria1:="Completed"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(R[-25]C[-1]<=0.33,""On
time"",""Missed"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
Selection.FillDown

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With


End Sub

Thanks for your help so far, I'm learning all the time


"Bernie Deitrick" wrote:

Anita,

I made a copy and paste error, and left off the last line:

Next myCell

Sorry about that,
Bernie
MS Excel MVP


"AnitaML" wrote in message
...
Hi Bernie

I gave this one a try, but I get an error VB wants me to use 'Next' if
i
use
'With'

"Bernie Deitrick" wrote:

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.

Selecting first cell in a colmn after a filter has bee applied
 

Anita,

Are you sure the cells are actually blank, and don't just look blank? A
space, a single quote, a formula that returns "" etc, all look blank but
aren't actually blank.

The code as written doesn't care which sheet is active, and works on the
sheet "Build PE Config" So there are other issues with your workbook beyond
the code.

HTH,
Bernie
MS Excel MVP

"AnitaML" wrote in message
...
Hi Bernie

Yep that would be me - confused always

Anway, I tried you code and it seems to get as far a applying the filter,
and finding the first correct cell, but it doesn't insert the formula into
the relevant cells in column I. Also, I have about 35 tabs in this
spreadsheet and if it is on anther tab when I run the macro, it doesn't do
anything at all. Once I have this macro working how I want, I want to be
able
to repeat it for most of the other tabs.

Thanks for you help.

Anita

Anita

"Bernie Deitrick" wrote:

Anita,

Your code seems a little confused, so I'm going to go out on a limb and
ask
that you try this code below. I think it does what you want - or at
least
the first part of what you want.

HTH,
Bernie
MS Excel MVP


Sub BacklogFormula2()

Dim myRange As Range
Dim mySht As Worksheet

On Error Resume Next

Set mySht = Sheets("Build PE Config")
Set myRange = mySht.Range("A1").CurrentRegion

If Not mySht.AutoFilterMode Then myRange.AutoFilter
myRange.AutoFilter Field:=3, Criteria1:="Ready"
Intersect(mySht.Range("I:I"), myRange). _
SpecialCells(xlCellTypeVisible). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"

myRange.AutoFilter
End Sub





"AnitaML" wrote in message
...
HI Bernie

When I add this code to my macro, it fills the first cell, then gives
me a
message to tell me what the first cell is, but it exits out and does
not
run
the rest of my macro and it doesn't fill all the other cells in the
row
related to filter I've applied.

Below is the Macro as I have it so farThis works quite well, only the
first
loop doesn't stop and it eventually comes back with an error (after it
has
gone down the whole column!)

Sub BacklogFormula()


Sheets("Build PE Config").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Ready"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With

Selection.AutoFilter Field:=3, Criteria1:="Completed"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(R[-25]C[-1]<=0.33,""On
time"",""Missed"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
Selection.FillDown

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With


End Sub

Thanks for your help so far, I'm learning all the time


"Bernie Deitrick" wrote:

Anita,

I made a copy and paste error, and left off the last line:

Next myCell

Sorry about that,
Bernie
MS Excel MVP


"AnitaML" wrote in message
...
Hi Bernie

I gave this one a try, but I get an error VB wants me to use
'Next' if
i
use
'With'

"Bernie Deitrick" wrote:

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.

Selecting first cell in a colmn after a filter has bee applied
 

Anita,

Probably be best if you sent me a pared down version of your file.

HTH,
Bernie
MS Excel MVP

"AnitaML" wrote in message
...
Hi

No there is nothing in them at all, before I ran the macro, I highlighted
the whole column and cleared the contents by hitting the delete key.

Anita

"Bernie Deitrick" wrote:

Anita,

Are you sure the cells are actually blank, and don't just look blank? A
space, a single quote, a formula that returns "" etc, all look blank
but
aren't actually blank.

The code as written doesn't care which sheet is active, and works on the
sheet "Build PE Config" So there are other issues with your workbook
beyond
the code.

HTH,
Bernie
MS Excel MVP

"AnitaML" wrote in message
...
Hi Bernie

Yep that would be me - confused always

Anway, I tried you code and it seems to get as far a applying the
filter,
and finding the first correct cell, but it doesn't insert the formula
into
the relevant cells in column I. Also, I have about 35 tabs in this
spreadsheet and if it is on anther tab when I run the macro, it
doesn't do
anything at all. Once I have this macro working how I want, I want to
be
able
to repeat it for most of the other tabs.

Thanks for you help.

Anita

Anita

"Bernie Deitrick" wrote:

Anita,

Your code seems a little confused, so I'm going to go out on a limb
and
ask
that you try this code below. I think it does what you want - or at
least
the first part of what you want.

HTH,
Bernie
MS Excel MVP


Sub BacklogFormula2()

Dim myRange As Range
Dim mySht As Worksheet

On Error Resume Next

Set mySht = Sheets("Build PE Config")
Set myRange = mySht.Range("A1").CurrentRegion

If Not mySht.AutoFilterMode Then myRange.AutoFilter
myRange.AutoFilter Field:=3, Criteria1:="Ready"
Intersect(mySht.Range("I:I"), myRange). _
SpecialCells(xlCellTypeVisible). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"

myRange.AutoFilter
End Sub





"AnitaML" wrote in message
...
HI Bernie

When I add this code to my macro, it fills the first cell, then
gives
me a
message to tell me what the first cell is, but it exits out and
does
not
run
the rest of my macro and it doesn't fill all the other cells in
the
row
related to filter I've applied.

Below is the Macro as I have it so farThis works quite well, only
the
first
loop doesn't stop and it eventually comes back with an error
(after it
has
gone down the whole column!)

Sub BacklogFormula()


Sheets("Build PE Config").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Ready"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]<=0.33,""Pipeline"",""Backlog"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With

Selection.AutoFilter Field:=3, Criteria1:="Completed"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
ActiveCell.FormulaR1C1 = "=IF(R[-25]C[-1]<=0.33,""On
time"",""Missed"")"
Cells(Range("I1").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row, 9).Select
Selection.FillDown

Dim i As Long

With Selection
Do Until .Offset(i, 0).Value <= 0
i = i + 1
.Copy Destination:=.Offset(i, 0)
Loop
End With


End Sub

Thanks for your help so far, I'm learning all the time


"Bernie Deitrick" wrote:

Anita,

I made a copy and paste error, and left off the last line:

Next myCell

Sorry about that,
Bernie
MS Excel MVP


"AnitaML" wrote in message
...
Hi Bernie

I gave this one a try, but I get an error VB wants me to use
'Next' if
i
use
'With'

"Bernie Deitrick" wrote:

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.