+ Reply to Thread
Results 1 to 9 of 9

create a for loop within a for loop

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    create a for loop within a for loop

    I created this loop below. Now I need to add code that will divide Range ("C" & i) by the closest cell (going down, not up) with formatting that has an upper line border. How can I add this?


    Sub mil()
    For i = 3 To 15
    If Range("C" & i).Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then
    Else
    Range("D" & i) = Range("C" & i) 'Divide by the closest cell with a top border
    End If
    Next
    End Sub

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: create a for loop within a for loop

    Maybe this :

    Sub mil()
        Dim k As Long
    
        For i = 3 To 15
            If Range("C" & i).Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then
            Else
                For k = i + 1 To 15
                    If Range("C" & k).Borders(xlEdgeTop).LineStyle = xlContinuous Then
                        Range("D" & i) = Range("C" & i) / Cells("C" & k)                                   'Divide by the closest cell with a top border
                        Exit For
                    End If
                Next k
            End If
        Next
    End Sub
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: create a for loop within a for loop

    Nope. Here is an example of the file that I want to use. Returned values in column B.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: create a for loop within a for loop

    Much better when we can see the file. Macro is totally different.
    Try this instead :

    Sub test()
        Dim rg As Range, c As Range
        
        For Each rg In Columns(1).SpecialCells(2, 1).Areas
            For Each c In rg
            With c
                .Offset(0, 1).Formula = "=" & .Address(0, 0) & "/" & rg.Cells(rg.Rows.Count).Address
            End With
            Next c
        Next rg
        
    End Sub

  5. #5
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: create a for loop within a for loop

    This works perfectly. Can you explain what each piece is doing? Thanks.

  6. #6
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: create a for loop within a for loop

    It finds all areas separated by a blank row and the adds the formula.
    I've added some comments in the code.
    If you need more help, try using the F1 key (help) on the keywords such as .Address, .SpecialCells, etc...

    Sub test()
        Dim rg As Range, c As Range
        
        ' Columns(1).SpecialCells(2, 1).Areas
        ' In column 1, find all areas that contain cells that are constants (2) and numbers (1) only
        ' Loop through each area (rg) of the areas found
        For Each rg In Columns(1).SpecialCells(2, 1).Areas
            'for each cell (c) in the range area (rg)
            For Each c In rg
                With c
                    'offset 1 column formula = adress of the cells (rows and columns relative (0) and divide by
                    '                          the last cell in the area (rg.cells(rg.rows.count) (rows and columns absolute (1 per default))
                    .Offset(0, 1).Formula = "=" & .Address(0, 0) & "/" & rg.Cells(rg.Rows.Count).Address
                End With
            Next c
        Next rg
        
    End Sub

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: create a for loop within a for loop

    Here's a version with one loop.
        Dim rg As Range
        
        For Each rg In Columns(1).SpecialCells(2, 1).Areas
            rg.Offset(0, 1).FormulaR1C1 = "=RC[-1]/R" & rg.Cells(rg.Rows.Count).Row & "C[-1]"
        Next rg
    If posting code please use code tags, see here.

  8. #8
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: create a for loop within a for loop

    I tried this code with this format and it didn't work. Why? Spreadsheet attached.
    Attached Files Attached Files

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: create a for loop within a for loop

    Which code are you referring to and how did it not work?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I want to create loop with a loop
    By wolfdemon710 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-28-2015, 03:33 PM
  2. Loop through multiple files and call macros (but unable to loop)
    By ryanpetersen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2014, 12:04 PM
  3. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  4. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM
  5. How do I create a For loop within a For loop?
    By Linking to specific cells in p in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2005, 06:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1