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

[AnitaML]

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.

 

[Bernie Deitrick]

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

 

[Tom Ogilvy]

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).column + 1).Select
'or cells(rng2(1).Row,9).Select ' the first is more flexible
else
msgbox "No items matched filter criteria"
End if

 

[Bernie Deitrick]

Anita,

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

Next myCell

Sorry about that,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

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

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

Anita,

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

Next myCell

Sorry about that,
Bernie
MS Excel MVP


i
use are
8 then
I included in
the criteria,
and select
the

 

[AnitaML]

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

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

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

Anita,

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

Next myCell

Sorry about that,
Bernie
MS Excel MVP


Hi Bernie

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

:

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

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.

 

[Bernie Deitrick]

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

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

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

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


:

Anita,

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

Next myCell

Sorry about that,
Bernie
MS Excel MVP


Hi Bernie

I gave this one a try, but I get an error VB wants me to use

'Next' if
i

use
'With'

:

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

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.

 

[Bernie Deitrick]

Anita,

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

HTH,
Bernie
MS Excel MVP

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

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

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

:

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





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


:

Anita,

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

Next myCell

Sorry about that,
Bernie
MS Excel MVP


Hi Bernie

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

:

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

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.