Glaswegian
08-29-2014, 04:23 AM
Hi again
Another Friday problem - I have a list of data in the range B9 to L100 and users are allowed to filter the list (dates in col B) by choosing a month from a userform. This all works fine.
However, I need to find the value of L10 (or what is the equivalent of L10 after filtering) and this is where I have a problem.
The filtering is part of a Print routine that prints out the filtered list. The piece of code that does not work is
With Sheets("Statements")
.AutoFilterMode = False
.Range("B9:B100").AutoFilter Field:=1, Criteria1:=myText, Operator:=xlFilterDynamic
.Range("L9").Value = .Range("L9").Offset(1, 0).SpecialCells(xlCellTypeVisible).Value
End With
Something to do with my use of SpecialCells? I've tried some other combinations but no luck.
There are various other operations and functions within the workbook as a whole but this is the only bit I cannot work out.
Any help greatfully appreciated.
Another Friday problem - I have a list of data in the range B9 to L100 and users are allowed to filter the list (dates in col B) by choosing a month from a userform. This all works fine.
However, I need to find the value of L10 (or what is the equivalent of L10 after filtering) and this is where I have a problem.
The filtering is part of a Print routine that prints out the filtered list. The piece of code that does not work is
With Sheets("Statements")
.AutoFilterMode = False
.Range("B9:B100").AutoFilter Field:=1, Criteria1:=myText, Operator:=xlFilterDynamic
.Range("L9").Value = .Range("L9").Offset(1, 0).SpecialCells(xlCellTypeVisible).Value
End With
Something to do with my use of SpecialCells? I've tried some other combinations but no luck.
There are various other operations and functions within the workbook as a whole but this is the only bit I cannot work out.
Any help greatfully appreciated.