title: Return Column index of the Active Cell so it can be manipulated to form a new Range
body: <p>I am trying to have a common cell where the calculation always starts then I want to translate the range depending on the date and how many weeks have passed since the spreadsheet was last updated. </p>
  
  <p>for example Active Cell column index is 4 and 12 weeks have passed. So the Active Cell needs to now be column 16. Then I would like to select 12 Columns to the right and all used rows.</p>
  
  <pre><code>$ Sub DataUpdate()
  
  
  Dim WeekCom As Double
  Dim WeekUpd As Double
  Dim DatSt As Range
  Dim DifWks As Double
  
  
  WeekCom = Int("WeekCommencing")
  WeekUpd = Int("Lastupdate")
  DifWks = WeekCom - WeekUpd
  Set DatSt = Range("DataStart")
  
  If WeekCom &gt; WeekUpd + 7 Then
  With ActiveSheet
      .Range(Cells(row(DatSt), = Column(DatSt)+ DifWks), Cells
             (Cells.Rowindex.SpecialCells(xlCellTypeLastCell), = Column(DatSt)+ DifWks+12))
  
  End With
  
  End If
  
  End Sub $
  </code></pre>
  
  <p>This is what I have so far I'm not sure if it's any use as I am new to VBA. </p>
  
  <p>Regards</p>
  
  <p>Greg</p>
  
  <pre><code>Sub DataUpdate()
  
  Dim WeekCom As Long
  Dim WeekUpd As Long
  Dim DifWks As Long
  Dim DatSt As Range
  Dim newRange As Range
  
  **WeekCom = CInt(Range("WeekCommencing").Value)**
  **WeekUpd = CInt(Range("Lastupdate").Value)**
  DifWks = WeekCom - WeekUpd
  Set DatSt = Range("DataStart")
  
  If WeekCom &gt; WeekUpd + 7 Then
      Set newRange = Intersect(DatSt.Offset(0, DifWks).Resize(1, DifWks).EntireColumn, ActiveSheet.UsedRange)
  End If
  
      MsgBox newRange.Address
  
  End Sub
  </code></pre>
  
  <p>This code still seems to be providing an error of "Overflow". the only data it is currently using is dates formatted as general so up in the 40 thousands. @MacroMan_ Sorry to keep bothering you but I hope you can help. I have put asterisks around where it's showing an error. 
  Cheers
  Greg</p>