2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.


Partial code follows:


Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria


up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula


I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.


In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.


I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

Check your other post.

Dennis wrote:

2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.

Partial code follows:

Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

up to this point all is well

Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula

I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.

In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.

I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

--

Dave Peterson

I think Dave Peterson got it for me in Excel.Misc

Thanks all

Dennis wrote:
2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.


Partial code follows:


Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria


up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula


I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.


In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.


I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

Sub firstcellinfilteredrng()
Range("a2:a22").SpecialCells(xlVisible).Cells(1, 1).Select
End Sub

--
Don Guillett
SalesAid Software

"Dennis" wrote in message
oups.com...
2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.


Partial code follows:


Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria


up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula


I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.


In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.


I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

Thanks Don!!!

Dennis


Don Guillett wrote:
Sub firstcellinfilteredrng()
Range("a2:a22").SpecialCells(xlVisible).Cells(1, 1).Select
End Sub

--
Don Guillett
SalesAid Software

"Dennis" wrote in message
oups.com...
2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.


Partial code follows:


Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria


up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula


I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.


In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.


I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

Glad to help

--
Don Guillett
SalesAid Software

"Dennis" wrote in message
oups.com...
Thanks Don!!!

Dennis


Don Guillett wrote:
Sub firstcellinfilteredrng()
Range("a2:a22").SpecialCells(xlVisible).Cells(1, 1).Select
End Sub

--
Don Guillett
SalesAid Software

"Dennis" wrote in message
oups.com...
2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.


Partial code follows:


Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria


up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula


I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.


In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.


I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

Dave,
I am having a problem with both proposals.

With yours, I need the cell formula to be exactly as the formula in
"effectively" "E2"
all the way for the filtered cwells. Currently I am getting formulas
which are incrementing.

TIA Dennis


Dave Peterson wrote:
Check your other post.

Dennis wrote:

2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.

Partial code follows:

Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

up to this point all is well

Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula

I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.

In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.

I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

--

Dave Peterson

What's the formula in one of the cells in E2 (after you filter)?

Dennis wrote:

Dave,
I am having a problem with both proposals.

With yours, I need the cell formula to be exactly as the formula in
"effectively" "E2"
all the way for the filtered cwells. Currently I am getting formulas
which are incrementing.

TIA Dennis

Dave Peterson wrote:
Check your other post.

Dennis wrote:

2003

Until today, I have never asked for ASAP! To make it worse this is the
second posting.
Send me the Crow and I'll eat it! I have to "make this work or else"
?!? NO kidding

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.

Partial code follows:

Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

up to this point all is well

Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula

I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.

In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible

cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.

I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

--

Dave Peterson

--

Dave Peterson