Nested Case Statements that Return Values back onto Excel

[sun56]

I'm new to VBA and am trying to write a macro that tells me insurance premiums for a given accident based on the the accident date and state.

Ex.

1/1/2009 to 12/31/2009 for AK = $1000, CA = $4000, and all other states = $600
1/1/2010 to 12/31/2010 for MN = $500, all other states = $300
1/1/2011 to 12/31/2010 for AZ = $5300, all other states = $5000

I want to be able to push a button and have this information pull into a certain column.

Can someone give me pointers on my code snippet?

________________

Please Login or Register  to view this content.

_____________

If nested case statements isn't the best way to go, please point me in the right direction.
Thanks!

[stnkynts]

Can someone give me pointers on my code snippet?

Yeah, delete it. Extremely flawed.

Submit an example workbook which clearly demonstrates what you are trying to accomplish.

[sun56]

I started from scratch and got my code working the way I want for a specific line. How can I "generalize" my code so that it runs until there are no more dates to check (column c)? Should I use an If statement? Furthermore, how should I change my code so that results pull in the first empty column?

[sun56]

Here's the code:

Option Explicit

Private Sub Test_Click()

Select Case Range("C7").Value

Case "39814" To "40178" ‘converted dates to integers
If Range("D7").Value = "AK" Then
Range("AC7").Value = "1000"
If Range("D7").Value = "CA" Then
Range("AC7").Value = "4000"
Else
Range("AC7").Value = "600"
End If

Case “40179” To “40543”
If Range("D1") = "MN" Then
Range("AC1").Value = 500
Else
Range("AC1").Value = 300
End If

Case “40544” To “40908”
If Range("D1") = "AZ" Then
Range("AC1").Value = 5300
Else
Range("AC1").Value = 5000
End If

End Select

End Sub