VBA Looping Input Range and Output Range

[Whitley]

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 until the last column (i.e. lColumn).

Thanks in advance for any help.

[Excel Fox]

Whitley, welcome to ExcelFox

You will be better off uploading a sample workbook as it will be easier for the volunteers here to relate to the explanation given above.

[Excel Fox]

Just for the sake of developers

VBA Looping Input Range and Output Range (http://www.excelforum.com/excel-programming-vba-macros/915477-vba-looping-input-range-and-output-range.html?p=3200049)
vba - Linking input and output ranges when using a loop - Stack Overflow (http://stackoverflow.com/questions/16042662/linking-input-and-output-ranges-when-using-a-loop)
http://www.excelfox.com/forum/f2/vba-looping-input-range-output-range-909/
VBA Looping Input Range and Output Rang (http://www.ozgrid.com/forum/showthread.php?t=177603)

Whitley, if you cross-post your query across multiple forums, be sure to mention that in all the forums. Most of these online communities have specific rules regarding cross-posting. The reason why it's a sensitive thing to do is because of the potential waste of time by the developers here who are voluntarily helping out the online community with their expertise. X-posting is like booking taxi cabs from 10 different vendors, and hoping in the first one that arrives! Certainly doesn't go well with the developers spending time trying to solve your query, when it would have already been solved or is getting solved by another developer on another forum. So if you have to X-post, do so, but please state that clearly in your post. Thanks for understanding.

[Whitley]

My apologies for the "Hail Mary" post, but I've been working on this project for several months now and need to draw it to a conclusion very soon. I know I'm close, I just need a little code to finish it up. How do I attach a workbook? Again, any help is much much appreciated.

[Whitley]

I tried, but I can't pare it down any further (105.9 KB) to meet the size requirements for the upload (102.5 KB). Suggestions?

[Excel Fox]

Yes, zip it and upload

[Whitley]

OK, find the ZIP'd file attached. Thanks

[Whitley]

Just a quick note to close the loop. I worked out the following solution, and would appreciate any suggestions to make it more efficient. Thanks for everyone's consideration.


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