PDA

View Full Version : Solved: Check cells in an AutoFiltered Range



Solidus85
07-18-2008, 10:02 AM
Hello, I am trying to compare rows against one another in an autofiltered range. I have five fields: Date, Company, Risk Rating, Note Number, and Default. I am trying to calculate the rate of default for notes of a particular company (I work for a holding company with many subsidiaries) per month. In order to do this, I need to filter out only two months and only one company, and then I need to compare each risk rating. If there is a default, I need to mark it in the default field so that a note may not default more than once (in future months).

The one thing that I am struggling with is forcing excel to only look at my filtered range. I can set up the autofilter and I can do all of my calculations, but only if I copy the autofiltered range to another spreadsheet and then delete it afterwards. I would like to do everything on that filtered range. Here is the relevent portion of my code:


Dim theDate As Date 'The date in question
Dim theDateMinusOne As Date 'The month before
Dim theCompany As String 'The company in question
Dim dSheet As Variant 'Workbook
Dim dBook As Workbook 'Worksheet
Dim nCount As Long 'Number of Notes Total
Dim dnCount As Long 'Number of Defaulted Notes
Dim countRange As Range 'Count Range
Dim baseRange As Range 'Selectable Range

'Autofilter the company and the dates
ActiveSheet.AutoFilterMode = False

'This is the original range (the entire range)
Set baseRange = dSheet.Range("A2", dSheet.Range("C65536").End(xlUp).Address)

'Filter out what we want
With baseRange

.AutoFilter Field:=2, Criteria1:=theCompany
.AutoFilter Field:=1, Criteria1:=theDate, Operator:=xlOr, Criteria2:=theDateMinusOne

End With

'Check every row to see if the notes are the same, and if they are, is there a default
For Each countRange In baseRange

MsgBox countRange.Row

'If they are the same then
If dSheet.Range("C" & countRange.Row) = dSheet.Range("C" & countRange.Row) Then

'Increase the number of notes
nCount = nCount + 1

'Check to see if note has already defaulted
If dSheet.Range("E" & countRange.Row - 1) = "Y" Then

dSheet.Range("E" & countRange.Row) = "Y"
GoTo Skip

End If

'Check for default
If dSheet.Range("D" & countRange.Row) > dSheet.Range("D" & countRange.Row) Then

'We have a defaulted note
dnCount = dnCount + 1
dSheet.Range("E" & countRange.Row).Value = "Y"

End If

End If

Skip:

Next countRange
With the way the code is written right now, I realize that each row will be counted three times, which I know how to fix and is not a concern to me right now. What I am trying to avoid is having every row counted and not just the filtered rows.

Field A: Date
Field B: Company
Field C: Note Number
Field D: Risk Rating
Field E: Default

If anyone could offer help I'd be most appreciative. I'll be unavailable for the weekend but I'll be back to check on this later.

Bob Phillips
07-18-2008, 11:59 AM
For Each countRange In baseRange.SpecialCells(xlCellTypeVisible)

Solidus85
07-21-2008, 06:29 AM
It seems to work, thanks a bunch.