I'm new to VBA and need help writing a macro given the following
A. Enddate = .Cell(lRow, 7).Value.
B. AnnualDepr = ".Cell(lRow, 11).Value.
C. PartialYearDepr = "(.Cell(lRow, 11).Value) /12) * (DateDiff("m", EndDate, FY)).
D. FY or Input Range = range of fiscal year end dates (i.e. 10/31/13 - 10/31/75), Range(Cells(2, 14), Cells(2, lColumn)).
E. Output range = Range(Cells(lRow, 14), Cells(lRow, lColumn)).
Here's the set-up:
If the "Year(EndDate) > Year(FY)" then copy AnnualDepr to the appropriate cells in Output Range.
ElseIf "Year(EndDate) = Year(FY)" then use PartialYearDepr in the appropriate cell in Output Range.
Else Place 0 in the remaining cells in Output Range.
It's important that "AnnualDepr" and "PartialYearDepr" appear beneath the correct FY, and I can't seem to get that right (i.e. linking the Input Range (i.e. Range(Cells(2, 14), Cells(2, lColumn))) and Output Range (i.e. Range(Cells(lRow, 14), Cells(lRow, lColumn))).
I've tried looping with 'For Each', but once the first argument is satisfied (i.e. Year(EndDate) > Year(FY)), AnnualDepr is copied across the entire Output Range, INSTEAD of only copying up to the cell where Year(EndDate) = Year(FY) occurs.
PartialYearDepr should only appear when Year(EndDate) = Year(FY) occurs.
After both arguments are satisfied (i.e. Year(End Date) > = Year(FY)), 0 should appear in the Output Range till the last column (i.e. lColumn).
Thanks in advance for any help.
'Post Annual Depreciation thru EDate, thereafter 0 Dim lColumn As Long lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
Dim EDate As Date
EDate = .Cells(lRow, 7).Value
Dim rgLife As Range
Dim rgDeprec As Range
Dim i As Long, j As Long
Set rgDeprec = Range(Cells(lRow, 14), Cells(lRow, lColumn))
Set rgLife = Range(Cells(2, 14), Cells(2, lColumn))
For i = 1 To rgLife.Rows.Count
For j = 1 To rgLife.Columns.Count
If Year(EDate) > Year(rgLife.Cells(i, j).Value) Then
Cells(lRow, 11).Copy rgDeprec.Cells(i, j)
ElseIf Year(EDate) = Year(rgLife.Cells(i, j).Value) Then
rgDeprec.Cells(i, j) = Cells(lRow, 10)_
- WorksheetFunction.Sum(Range(Cells(lRow, 12), Cells(lRow, lColumn)))
End If
Next j
Next i
