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?
________________
Option Explicit
'Pulls Date for Macro to Run
Sub GetDate()
Dim MyDate As Integer
MyDate = Range("C:C").Value
End Sub
'Pulls State Info for Macro to Run
Sub GetState()
Dim MyState As String
MyState = Range("D:D").Value
End Sub
Private Sub CommandButton21_Click() '<-This line pulls up with an error message
Dim ws As Worksheet
Set ws = Worksheets("Report 1")
Set ws = ActiveSheet
Select Case Date
Case 1 / 1 / 2009 To 12 / 31 / 2009
If State = "AK" Then SIR = 1000 '<- This line pulls up as "Compile error: variable note defined
If State = "CA" Then SIR = 4000
Else
SIR = 600
Case 1 / 1 / 2010 To 1 / 31 / 2010
If State = "MN" Then SIR = 500
Else
SIR = 300
End Select
End Sub
_____________
If nested case statements isn't the best way to go, please point me in the right direction.
Thanks!
Bookmarks