Having difficulty to determine how to address in VBA the first visible cell in Column "D" after the FilterCriteria changes.
Partial code is as follows:
Code:
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
Code:
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.
Bookmarks