Results 1 to 8 of 8

Thread: VBA Looping Input Range and Output Range

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0

    VBA Looping Input Range and Output Range

    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.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just for the sake of developers

    VBA Looping Input Range and Output Range
    vba - Linking input and output ranges when using a loop - Stack Overflow
    http://www.excelfox.com/forum/f2/vba...put-range-909/
    VBA Looping Input Range and Output Rang

    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    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.

  5. #5
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    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?

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Yes, zip it and upload
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    OK, find the ZIP'd file attached. Thanks
    Attached Files Attached Files

  8. #8
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    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.

    Code:
    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
    Last edited by Admin; 04-26-2013 at 09:36 AM.

Similar Threads

  1. Copy Table Range Till Last Row Using VBA
    By ivandgreat in forum Excel Help
    Replies: 2
    Last Post: 05-09-2013, 05:41 PM
  2. Range Difference VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 03-21-2012, 05:58 PM
  3. Split Range into Multiple Columns VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 03-07-2012, 10:53 PM
  4. Mail Range or Selection using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 11-03-2011, 09:00 AM
  5. Replies: 2
    Last Post: 10-05-2011, 04:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •