Ranking data based on set criteria

Hi,

I am trying to create a macro that ranks my data based on criteria in 3 different fields.

For each state (column A) for each month (column C) if Column L = "Y" then rank based on values in column J in column Q.

eg. for NSW, I want to see the revenue ranked highest to lowest for each month with the rank placed in column Q.

I have attached some sample data, and the code is below.

I have not been able to get the macro to work (I'm sure those with knowledge could pin point the problem straight away) and that is only based on trying to get the all of the data ranked based on state, without the other criteria needed.

Any help would be much appreciated.

Book1.xlsx

Sub Rank_By_State()
Dim lastRw, firstRw, nxtRw As Integer
Dim myState As Range
'Determine last row in table
 lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Intialize Row Counter
  firstRw = 2
'Loop through range looking for change in Column A
   For Each myState In Range("A2:A" & lastRw)
'Increment last row counter
    nxtRw = nxtRw + 1
'Build and Place formula at change of value
     If myState <> myState.Offset(1, 0) Then
      Range("Q" & firstRw & ":Q" & firstRw + nxtRw - 1).Formula = _
       "=RANK(RC[-7],R" & firstRw & "A3:R" & firstRw + nxtRw - 1 & "A3)"
'Set start row for next set of values
         firstRw = firstRw + nxtRw
'Reset last row counter
         nxtRw = 0
     End If
   Next
End Sub