Get the last loop in a for loop

[aRush113]

Good day everyone.
Is there a way to find the last iteration of a for-loop?
I have a for loop that creates a list of used dates and time in a column but i need to add a different time to just the end date in the list.

1/1/2024	3/1/24	1/1/24 14:30:00	start date1
3/1/24	6/1/24	2/1/24 14:30:00	list of between dates created by loop
		3/1/24 10:30:00	end date1
		3/1/24 14:30:00	start date2
		4/1/24 14:30:00	list of between dates created by loop
		5/1/24 14:30:00	list of between dates created by loop
		6/1/24 10:30:00	end date2

The end date needs to be different than the start date of the next list or it will be counted twice and it only HAS to be a counted once.

Basically i need the last iteration number of the loop to change the time.

Thanx in advance.

 

[rlv01]

Is there a way to find the last iteration of a for-loop?

Since a FOR loop almost always has a defined number of iterations, it is fairly straightforward to find the last iteration:

Sub Misc()
    For I = 1 To 100
        '
        ' Loop code goes here
        '
        If I = 100 Then
            'last loop
            MsgBox "This is the last iteration!"
            '
            'Special code goes here
            '
        End If
    Next I
End Sub

 

[MARK858]

I have a for loop that creates a list of used dates

Can you post the code for the loop please as it would help to see the syntax you are using i.e. there is a difference between

Dim i as long
For i = 1 to 100
'more code
Next i

and

Dim cell as Range
For Each cell in Range("B37:B234")
'more code
Next Cell

 

[aRush113]

Can you post the code for the loop please as it would help to see the syntax you are using i.e. there is a difference between

Dim i as long
For i = 1 to 100
'more code
Next i

and

Dim cell as Range
For Each cell in Range("B37:B234")
'more code
Next Cell

Test vba list of dates.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Room N°	Checkin	N° Nights	Checkout					Checkin Time	Checkout Time
2	5	31/08/23	3	03/09/23	31/08/2023 5 14:30	FALSO	5		14:30	10:30
3	2	07/07/23	4	11/07/23	01/09/2023 5 14:30	FALSO	5
4	1	07/07/23	9	16/07/23	02/09/2023 5 14:30	FALSO	5	There is a Worksheet_Change event that runs the macro when values change in columns A to C
5	5	14/07/23	3	17/07/23	03/09/2023 5 14:30	FALSO	5
6	2	28/08/23	6	03/09/23	07/07/2023 2 14:30	FALSO	2
7	2	14/07/23	2	16/07/23	08/07/2023 2 14:30	FALSO	2
8	3	14/07/23	1	15/07/23	09/07/2023 2 14:30	FALSO	2
9	5	09/08/23	7	16/08/23	10/07/2023 2 14:30	FALSO	2
10	4	18/07/23	14	01/08/23	11/07/2023 2 14:30	FALSO	2
11	1	26/05/23	4	30/05/23	07/07/2023 1 14:30	VERO	1	Column G is needed to generate the text in column E (Date + Room N° + Time)
12	5	04/08/23	2	06/08/23	08/07/2023 1 14:30	FALSO	1
13	5	22/04/23	3	25/04/23	09/07/2023 1 14:30	FALSO	1
14	2	14/08/23	1	15/08/23	10/07/2023 1 14:30	FALSO	1	The value in E11 is the first date of the Checkin date (It needs to have the Checkin Time Which is correct) but scrolling down to F284 i have the same date and room number but it beeing the Checkout date of that booking it needs to end with the Checkout time (10:30) thus not beeing an overbooking.
15	5	17/07/23	10	27/07/23	11/07/2023 1 14:30	FALSO	1
16	1	17/07/23	10	27/07/23	12/07/2023 1 14:30	FALSO	1
17	4	26/08/23	7	02/09/23	13/07/2023 1 14:30	FALSO	1
18	2	16/08/23	4	20/08/23	14/07/2023 1 14:30	FALSO	1
19	1	12/08/23	5	17/08/23	15/07/2023 1 14:30	FALSO	1
20	1	24/08/23	4	28/08/23	16/07/2023 1 14:30	FALSO	1
21	5	27/07/23	5	01/08/23	14/07/2023 5 14:30	FALSO	5
22	5	16/08/23	2	18/08/23	15/07/2023 5 14:30	FALSO	5
23	4	08/08/23	10	18/08/23	16/07/2023 5 14:30	FALSO	5
24	4	18/06/23	7	25/06/23	17/07/2023 5 14:30	VERO	5
25	1	02/08/23	9	11/08/23	28/08/2023 2 14:30	VERO	2	By changing the time value in cell F284 to 10:30 i won't get the overbooking I need the last iteration number of each loop to change the time from 14:30 to 10:30
26	2	02/08/23	10	12/08/23	29/08/2023 2 14:30	FALSO	2
27	2	20/08/23	2	22/08/23	30/08/2023 2 14:30	FALSO	2
28	5	08/07/23	1	09/07/23	31/08/2023 2 14:30	FALSO	2
29	5	18/08/23	7	25/08/23	01/09/2023 2 14:30	FALSO	2
30	1	20/08/23	3	23/08/23	02/09/2023 2 14:30	FALSO	2
31	4	20/08/23	4	24/08/23	03/09/2023 2 14:30	FALSO	2	Obviously this list changes everytime a booking is added or cancelled.
32	2	08/04/23	1	09/04/23	14/07/2023 2 14:30	FALSO	2
33	4	01/08/23	7	08/08/23	15/07/2023 2 14:30	FALSO	2
34	2	22/08/23	6	28/08/23	16/07/2023 2 14:30	FALSO	2
35	4	14/07/23	1	15/07/23	14/07/2023 3 14:30	FALSO	3
36	1	18/08/23	1	19/08/23	15/07/2023 3 14:30	FALSO	3
37	1	28/08/23	13	10/09/23	09/08/2023 5 14:30	FALSO	5
38	5	08/05/23	16	24/05/23	10/08/2023 5 14:30	FALSO	5
39	4	11/09/23	12	23/09/23	11/08/2023 5 14:30	FALSO	5
40	3	09/07/23	3	12/07/23	12/08/2023 5 14:30	FALSO	5
41	2	12/08/23	2	14/08/23	13/08/2023 5 14:30	FALSO	5
42	4	26/05/23	2	28/05/23	14/08/2023 5 14:30	FALSO	5
43	1	28/07/23	4	01/08/23	15/08/2023 5 14:30	FALSO	5
44	2	19/07/23	5	24/07/23	16/08/2023 5 14:30	VERO	5
45	2	29/07/23	1	30/07/23	18/07/2023 4 14:30	FALSO	4
46	1	02/07/23	5	07/07/23	19/07/2023 4 14:30	FALSO	4
47	4	10/07/23	4	14/07/23	20/07/2023 4 14:30	FALSO	4
Bookings

Cell Formulas
Range	Formula
D2:D47	D2	=IF(B2="","",B2+C2)
F2	F2	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E2)>1,TRUE,FALSE))
F3	F3	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E3)>1,TRUE,FALSE))
F4	F4	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E4)>1,TRUE,FALSE))
F5	F5	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E5)>1,TRUE,FALSE))
F6	F6	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E6)>1,TRUE,FALSE))
F7	F7	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E7)>1,TRUE,FALSE))
F8	F8	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E8)>1,TRUE,FALSE))
F9	F9	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E9)>1,TRUE,FALSE))
F10	F10	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E10)>1,TRUE,FALSE))
F11	F11	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E11)>1,TRUE,FALSE))
F12	F12	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E12)>1,TRUE,FALSE))
F13	F13	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E13)>1,TRUE,FALSE))
F14	F14	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E14)>1,TRUE,FALSE))
F15	F15	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E15)>1,TRUE,FALSE))
F16	F16	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E16)>1,TRUE,FALSE))
F17	F17	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E17)>1,TRUE,FALSE))
F18	F18	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E18)>1,TRUE,FALSE))
F19	F19	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E19)>1,TRUE,FALSE))
F20	F20	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E20)>1,TRUE,FALSE))
F21	F21	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E21)>1,TRUE,FALSE))
F22	F22	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E22)>1,TRUE,FALSE))
F23	F23	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E23)>1,TRUE,FALSE))
F24	F24	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E24)>1,TRUE,FALSE))
F25	F25	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E25)>1,TRUE,FALSE))
F26	F26	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E26)>1,TRUE,FALSE))
F27	F27	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E27)>1,TRUE,FALSE))
F28	F28	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E28)>1,TRUE,FALSE))
F29	F29	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E29)>1,TRUE,FALSE))
F30	F30	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E30)>1,TRUE,FALSE))
F31	F31	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E31)>1,TRUE,FALSE))
F32	F32	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E32)>1,TRUE,FALSE))
F33	F33	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E33)>1,TRUE,FALSE))
F34	F34	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E34)>1,TRUE,FALSE))
F35	F35	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E35)>1,TRUE,FALSE))
F36	F36	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E36)>1,TRUE,FALSE))
F37	F37	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E37)>1,TRUE,FALSE))
F38	F38	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E38)>1,TRUE,FALSE))
F39	F39	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E39)>1,TRUE,FALSE))
F40	F40	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E40)>1,TRUE,FALSE))
F41	F41	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E41)>1,TRUE,FALSE))
F42	F42	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E42)>1,TRUE,FALSE))
F43	F43	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E43)>1,TRUE,FALSE))
F44	F44	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E44)>1,TRUE,FALSE))
F45	F45	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E45)>1,TRUE,FALSE))
F46	F46	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E46)>1,TRUE,FALSE))
F47	F47	=IF(E2="",FALSE,IF(COUNTIF($E:$E,"="&E47)>1,TRUE,FALSE))

Named Ranges
Name	Refers To	Cells
OBCHECK	=Bookings!$E:$E	F2:F47

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:D	Cell Value	=$E$11	text	NO
E:E	Expression	=SE(E1="";FALSO;SE(CONTA.SE(E:E;"="&E1)>1;VERO;FALSO))	text	NO
F:F	Cell Value	=VERO	text	NO

Sub CalcOff()
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
Application.DisplayStatusBar = False
Application.CommandBars("ply").Enabled = False
End With
End Sub
Sub CalcOn()
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
Application.DisplayStatusBar = True
Application.CommandBars("ply").Enabled = True
End With
End Sub
Sub DateRange()

Dim ws As Worksheet
Dim startdate As Date
Dim enddate As Date
Dim ckInTime As Double
Dim ckOutTime As Date
Dim outputcell As Range
Dim RMoutputcell As Range
Dim Cell As Range
Dim x As Integer
Dim lRow As Integer
Dim outRow As Integer
Dim obRow As Integer

Set ws = Sheet1
CalcOff
Range("RMCHECK").ClearContents ' Room check
Range("OBCHECK").ClearContents ' Used dates
Range("TFCHECK").ClearContents ' Overbooked check

 lRow = Range("B2").End(xlDown).Row ' Last row Checkin column
 
 ckInTime = Range("ORACI")
' ckOutTime = Range("ORACO")
    
    For actrow = 2 To lRow
        If outRow = nil Then outRow = 2 Else outRow = Range("E2").End(xlDown).Row + 1
         startdate = Cells(actrow, 2)
         enddate = Cells(actrow, 4)
    
    Set outputcell = Cells(outRow, 5)
    Set RMoutputcell = Cells(outRow, 7)

         For x = 0 To enddate - startdate
             RMoutputcell.Offset(x) = Cells(actrow, 1)
             outputcell.Offset(x) = startdate + x & " " & RMoutputcell & " " & Format(ckInTime, "h:m")
         Next x
    
    Next actrow

 obRow = Range("E2").End(xlDown).Row
 
For k = 2 To obRow 'Place Overbooking Formula in column F
    ws.Range("F" & k).Formula = "=IF(E2="""",FALSE,IF(COUNTIF($E:$E" & ",""=""&E" & k & ")>1,TRUE,FALSE))"
'    ws.Range("F" & k).Formula = "=IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4))=INDIRECT(ADDRESS(ROW()-1,COLUMN()-1,4)),FALSE,COUNTIF($E$1:E" & k & ",""=""&E" & k & ")>1)"
Next k

    For j = 2 To k - 1
        vChk = Cells(j, 6)
        If vChk = "Falso" Then
            'do nothing
        Else
            'Show Overbooking message
            MsgBox "Overbooking"
            Exit Sub
        End If
        
    Next j
CalcOn
End Sub

 

[aRush113]

Found it, i'm using the column C value to establish the last iteration number for the loop and added

Dim itCount As Integer
    itCount = Cells(actrow, 3)

and changed the for loop to

         For x = 0 To enddate - startdate
             RMoutputcell.Offset(x) = Cells(actrow, 1)
             If x = itCount Then
             outputcell.Offset(x) = startdate + x & " " & RMoutputcell & " " & Format(ckOutTime, "h:m")
             Else
             outputcell.Offset(x) = startdate + x & " " & RMoutputcell & " " & Format(ckInTime, "h:m")
             End If
         Next x

Thanx to everyone for the help.
Peace