title: Calculating Due Dates based on Frequency using VBA
body: <p>So, right now I have this Excel sheet where there is a <em>last revision</em> date. I have named this column "LastRevisionDate". And then I have a column named "RevisionFrequency" . The "RevisionFrequency" contains a drop-down menu (data validation) consisting of terms, <code>Annually</code>, <code>Semi-Annually</code>, and <code>Quarterly</code>. And then I have a column where it states the "NextRevisionDate".</p>
  
  <p>So I want to write some VBA code that would calculate the <em>NextRevisionDate</em> from the <em>LastRevisionDate</em> and the <em>RevisionFrequency</em>.</p>
  
  <p>For example. Say in column "A" I have the RevisionFrequency to be <code>Semi-Annually</code>, And the last revision date was <code>Mar-14</code> in column "B", then I would want the <em>NextRevisionDate</em> in column "C" to state <em>September</em>. That's basically saying that the item gets revised twice a year.</p>
  
  <p>So I would want to create a macro where Column "C" is based off the <em>RevisionFrequency</em> and <em>LastRevisionDate</em>. I realize I could do this with a formula, but I have new items being added constantly so I do not want to keep copying formulas into each cell. Also for some items, they do not need revision, I would also like to have a blank cell if there is no <em>LastRevisionDate</em>.</p>
  
  <p>So far, I have this code:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  
  Dim ws As Worksheet
   Set ws = Sheets(1)
  'For this reference of the Column Named LastCalDate I am getting an error 
  If Not Intersect(Target, ws.Range("LastCalDate").Value) Is Nothing Then
  
  Dim Lastdate As Date
   Dim DueDate As Variant
   Dim Frequency As String
   Dim R As Variant
   Dim C As Variant
   Dim R1 As Variant
   Dim C1 As Variant
   Dim R2 As Variant
   Dim C2 As Variant
  
  
  
  
  R = Range("LastCalDate").Row
   C = Range("LastCalDate").Column
  
  R1 = Range("CalDueDate").Row
   C1 = Range("CalDueDate").Column
  
  R2 = Range("CalFrequency").Row
   C2 = Range("CalFrequency").Column
  
  Lastdate = Cells(R, C).Value 'Last Cal Date
   DueDate = Cells(R1, C1).Value 'Cal Due Date
   Frequency = Cells(R2, C2)
  
  If Frequency = "Annually" Then
  
  DueDate = DateAdd("mmm", 12, Lastdate)
  
  End If
  
  If Frequency = "Semi-Annually" Then
   DueDate = DateAdd("mmm", 6, Lastdate)
   End If
  
  If Frequency = "Quarterly" Then
   DueDate = DateAdd("mmm", 3, Lastdate)
   End If
  
  
  
  End Sub
  </code></pre>
  
  <p>This is what I have so far. I'm not sure If I am doing this correctly?</p>