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.

[SMChacko]

Just for the sake of developers

http://www.excelforum.com/excel-prog...html?p=3200049
http://stackoverflow.com/questions/1...n-using-a-loop
http://www.excelfox.com/forum/f2/vba...put-range-909/
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

[Leith Ross]

Hello SMChacko,

Thanks for posting the links to the other forums. I have never seen a list list long before.

[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.

[Leith Ross]

Hello Whitley,

It would be easier and faster to provide you with a solution if you posted your workbook. If you workbook contains sensitive information, please redact it before you post.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

[Whitley]

The workbook (4-17-2013.xlsm) is attached. Thanks again.

[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.

Please Login or Register  to view this content.