VBA to address first visible cell in Column "D" after filtering
 

2003

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.

VBA to address first visible cell in Column "D" after filtering
 

Maybe you could use something like:

dim myVRng as range
....

with worksheets("constants input")
with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible). cells.count = 1 then
'do nothing, only the header is visible
else
'go to column 5 (E) and come down a row and subtract a row to
'ignore the header
set myvrng = .columns(5).resize(.rows.count,1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)

myvrng.formular1c1 = myvrng.cells(1).formular1c1
end if
end with
end with

Untested, uncompiled. Watch for typos.

myVRng is the visible cells in column 5 of the filtered range. myVRng.cells(1)
is the first cell in that visible range.


EagleOne wrote:

2003

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

VBA to address first visible cell in Column "D" after filtering
 

Dave,

I think you have!! Thanks so much!!

Dennis

Dave Peterson wrote:
Maybe you could use something like:

dim myVRng as range
...

with worksheets("constants input")
with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible). cells.count = 1 then
'do nothing, only the header is visible
else
'go to column 5 (E) and come down a row and subtract a row to
'ignore the header
set myvrng = .columns(5).resize(.rows.count,1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)

myvrng.formular1c1 = myvrng.cells(1).formular1c1
end if
end with
end with

Untested, uncompiled. Watch for typos.

myVRng is the visible cells in column 5 of the filtered range. myVRng.cells(1)
is the first cell in that visible range.


EagleOne wrote:

2003

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