Help with loop please

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
717
Office Version
  1. 2016
Platform
  1. Windows
I've created a loop, everything is working, but now I need to put that loop inside of another loop.

The big loop that would begin just after formatting the Z column would be:

starting at X2, complete the below for each cell in column X and end at the last cell of data.

VBA Code:
Sub test3()
Dim rng, rng2, rng3, rng4 As Range, a, i As Long

 Set rng = Range("W2")
 Set rng2 = Range("AL2:AL" & Range("AL" & Rows.Count).End(xlUp).Row)
 Set rng3 = Range("AM2:AM" & Range("AL" & Rows.Count).End(xlUp).Row)
    
    Columns("Z:Z").Select
    Selection.NumberFormat = "m/d/yyyy"
 
' Start Loop
 If rng.Offset(0, 1).Value = 1 Then
    Range("Z2") = "=INDEX(" & rng3.Address & ",MATCH(G2," & rng2.Address & ",0))"
    rng.Offset(0, 2) = Range("Z2") - rng.Offset(0, -9)
 
 ElseIf rng.Offset(0, 1).Value > 1 Then
    a = rng.Offset(0, 1)
    For i = 1 To a
    rng.Offset(-1 + i, 3).FormulaArray = "=INDEX(" & rng3.Address & ", SMALL(IF(($G$2=" & rng2.Address & "), MATCH(ROW(" & rng2.Address & "), ROW(" & rng2.Address & ")), """"),ROWS($A$1:A" & i & ")))"
    rng.Offset(-1 + i, 4) = rng.Offset(-1 + i, 3) - Range("$N$2")
    Next i
        
 Set rng4 = Range("AA2:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
 
    rng.Offset(0, 2).FormulaArray = "=INDEX(" & rng4.Address & ",MATCH(MIN(ABS(" & rng4.Address & "-0)),ABS(" & rng4.Address & "-0),0))"

End If
    rng.Offset(0, 2).Copy
    rng.Offset(0, 2).PasteSpecial Paste:=xlPasteValues
    Columns("Z:AA").Clear

' End Loop

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I am not trying to answer your question but as I did a quick read through your post, I don't think you are declaring your variables the way you think you are. Your line of code below:

VBA Code:
Dim rng, rng2, rng3, rng4 As Range, a, i As Long

That line of code is the same as follows:

VBA Code:
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Range
Dim a As Variant
Dim i As Long

I believe you want rng, rng2, rng3 and rng4 all as Ranges and "a' as Long...

VBA Code:
Dim rng As Range, rng2 As Range, rng3 As Range, rng4 As Range, a As Long, i As Long
 
Upvote 0
I am not trying to answer your question but as I did a quick read through your post, I don't think you are declaring your variables the way you think you are. Your line of code below:

VBA Code:
Dim rng, rng2, rng3, rng4 As Range, a, i As Long

That line of code is the same as follows:

VBA Code:
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Range
Dim a As Variant
Dim i As Long

I believe you want rng, rng2, rng3 and rng4 all as Ranges and "a' as Long...

VBA Code:
Dim rng As Range, rng2 As Range, rng3 As Range, rng4 As Range, a As Long, i As Long
Thank you, I caught that later. I've since answered my own question. Learned a lot and found a lot of mistakes. Thank you.
 
Upvote 0
I figured it out :giggle:

VBA Code:
Sub Compare_Dates()
    Dim rng, rng2, rng3, rng4 As Range
    Dim LastRow, i, x As Long
    Dim Start, Last As Variant
    
    Start = InputBox("Starting Row?")
    Last = InputBox("Ending Row? (Last Row = 0")
    
    Dim StartTime   As Double
    Dim MinutesElapsed As String
    StartTime = Timer
    
    LastRow = Cells(Rows.Count, "X").End(xlUp).Row
    
    If Last = 0 Then
        Last = LastRow
    End If
    
    Set rng2 = Range("AL2:AL" & Range("AL" & Rows.Count).End(xlUp).Row)
    Set rng3 = Range("AM2:AM" & Range("AL" & Rows.Count).End(xlUp).Row)
    
    For x = Start To Last
        
        If Range("X" & x) = 1 Then
            Columns("Z:Z").NumberFormat = "m/d/yyyy"
            Range("Z2") = "=INDEX(" & rng3.Address & ",MATCH(" & Range("G" & x).Address & "," & rng2.Address & ",0))"
            Range("Y" & x) = Range("Z2") - Range("N" & x)
            
        ElseIf Range("X" & x) > 1 Then
            
            For i = 1 To Range("X" & x)
                Columns("Z:Z").NumberFormat = "m/d/yyyy"
                Range("Z" & i).FormulaArray = "=INDEX(" & rng3.Address & ", SMALL(IF((" & Range("G" & x).Address & "=" & rng2.Address & "), MATCH(ROW(" & rng2.Address & "), ROW(" & rng2.Address & ")), """"),ROWS($A$1:A" & i & ")))"
                Range("AA" & i) = Range("Z" & i) - Range("N" & x)
                
            Next i
            Set rng4 = Range("AA1:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
            
            Range("Y" & x).FormulaArray = "=INDEX(" & rng4.Address & ",MATCH(MIN(ABS(" & rng4.Address & "-0)),ABS(" & rng4.Address & "-0),0))"
            
        End If
        
        Range("Y" & x).Copy
        Range("Y" & x).PasteSpecial Paste:=xlPasteValues
        Columns("Z:AA").Clear
        
    Next x
    
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
    
End Sub
 
Upvote 0
Solution
I am glad you are squared away, but you did not fix your declarations...
 
Upvote 0
I figured it out :giggle:

VBA Code:
Sub Compare_Dates()
    Dim rng, rng2, rng3, rng4 As Range
    Dim LastRow, i, x As Long
    Dim Start, Last As Variant
   
    Start = InputBox("Starting Row?")
    Last = InputBox("Ending Row? (Last Row = 0")
   
    Dim StartTime   As Double
    Dim MinutesElapsed As String
    StartTime = Timer
   
    LastRow = Cells(Rows.Count, "X").End(xlUp).Row
   
    If Last = 0 Then
        Last = LastRow
    End If
   
    Set rng2 = Range("AL2:AL" & Range("AL" & Rows.Count).End(xlUp).Row)
    Set rng3 = Range("AM2:AM" & Range("AL" & Rows.Count).End(xlUp).Row)
   
    For x = Start To Last
       
        If Range("X" & x) = 1 Then
            Columns("Z:Z").NumberFormat = "m/d/yyyy"
            Range("Z2") = "=INDEX(" & rng3.Address & ",MATCH(" & Range("G" & x).Address & "," & rng2.Address & ",0))"
            Range("Y" & x) = Range("Z2") - Range("N" & x)
           
        ElseIf Range("X" & x) > 1 Then
           
            For i = 1 To Range("X" & x)
                Columns("Z:Z").NumberFormat = "m/d/yyyy"
                Range("Z" & i).FormulaArray = "=INDEX(" & rng3.Address & ", SMALL(IF((" & Range("G" & x).Address & "=" & rng2.Address & "), MATCH(ROW(" & rng2.Address & "), ROW(" & rng2.Address & ")), """"),ROWS($A$1:A" & i & ")))"
                Range("AA" & i) = Range("Z" & i) - Range("N" & x)
               
            Next i
            Set rng4 = Range("AA1:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
           
            Range("Y" & x).FormulaArray = "=INDEX(" & rng4.Address & ",MATCH(MIN(ABS(" & rng4.Address & "-0)),ABS(" & rng4.Address & "-0),0))"
           
        End If
       
        Range("Y" & x).Copy
        Range("Y" & x).PasteSpecial Paste:=xlPasteValues
        Columns("Z:AA").Clear
       
    Next x
   
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
   
End Sub
I am glad you are squared away, but you did not fix your declarations...
:unsure: Big picture I'm a newbie, but they look okay to me. I have explicit on, not getting any errors.

Thanks
 
Upvote 0
Wonderful.
I figured out (I think) what you're trying to get into my thick skull.

Only Variant can you put on one line with commas. Is that correct?

VBA Code:
    Dim rng As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Dim LastRow As Long
    Dim i As Long
    Dim x As Long
    Dim sq, sp, Start, Last As Variant
    Dim sh As Worksheet
 
Upvote 0
You can group them on one line but each one must be declared as the desired type. Please look at the example I posted in Post #2 at the bottom of the post. You can use 1 dim statement and then separate the variables with a comma, each one must be declared as the type. You can mix types on a single line.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top