Sunday, 29 September 2013

declare a variable Column based on a cover sheet

declare a variable Column based on a cover sheet

Dim iRow As Long
Dim CopyRange As String
iRow = (ActiveCell.Row)
Let CopyRange = "M" & iRow & ":" & "T" & iRow
Range(CopyRange).ClearContents
i have this code and i need to declare the second part of CopyRange ("T" &
iRow) as a variable based on my cover sheet.
Columns M to X respresent the 12 months, so whenever i change the cover
sheet to Septemper for example an Input sheet changes the value to 9, so
when i use the choose methods it chooses current month which is column U
in this case.
what i need to do is to clear all content but current month when i change
the cover, column M (jan) to column T (Aug), in this case.
i've been thinking of something like this:
Sheets("Inputs").Range("H3") = C
Let CopyRange = "M" & iRow
let iCopyRange = C-1 & iRow
Cells (CopyRange, iCopyRange).ClearContents
its kinda weird !!

No comments:

Post a Comment