vba-loop skip 7th day in a list

Question

I'm trying to populate a row of cells in a Calendar kind of way, where I skip the 7th Cell, that is the Sunday. The loop should continue writting values than on Monday or the 8th Cell. And So on for every week. The ranges of the cells varies depending on the given number of Days.

I tried setting my skipping condition using MOD for every 7th Day with the Loop value. MOD(number, divisor) = 0. If that checks out, no values should be added on the 7th cell but on the 8th.

The problem comes after the first sunday . My LoopValue is one "value" behind because of the offset. When it comes to the second sunday the condition is already one step behind, skipping the cells not on Sunday (14th day) but on Monday (16th) Day

I then tried "hard coding" the condition, to skip every 7,13,19,25,31,37th Day.. but it worked until the 25th Day.

Sub t1_Sunday4()
Dim in1Days, in2Value, jj1 As Integer
Dim os_skip, os_New As Integer
Dim x_ShortCondt As Boolean
os_New = 1
in1Days = Range("A3")
in2Value = Range("B3")

Sub t1()
  Dim in1Days, in2Value, jj1 As Integer
  Dim os_skip, os_New As Integer
  Dim x_ShortCondt As Boolean
  os_New = 1
  in1Days = Range("A3")
  in2Value = Range("B3")
  x_ShortCondt = False


For jj1 = 1 To in1Days
Cells(5, 5 + jj1) = jj1 ' # Iteration

If x_ShortCondt = True Then

    'MOD CONDITION
    If (jj1 Mod 7) = 0 Then ' Condition for every 7th Day
        os_skip = jj1 / 7
        os_New = jj1 + os_skip 'Off Set Cell number
    End If
    
Else

    'HARD CODING THE CONDITION
    If jj1 = 7 Or jj1 = 13 Or jj1 = 19 Or jj1 = 25 _
    Or jj1 = 31 Or jj1 = 37 Or jj1 = 37 Or jj1 = 43 _
    Or jj1 = 49 Then
        os_skip = jj1 / 7
        os_New = jj1 + os_skip
    End If
    
End If
   'POPULATE CELLS
    Cells(3, 5 + os_New) = in2Value ' Value to add in cell
    
    ' CONTROL LOOP AND OFFSET VALUES
    Cells(6, 5 + os_New) = jj1      ' Iteration for said Value after Offset
    Cells(7, 5 + os_New) = os_New   ' Value for Offset
    os_New = os_New + 1

Next jj1
End Sub

Answer 1

To save my workbook each week as a backup, I use the following:

If Weekday(Now) = (vbMonday) Or Day(Now) = 1 Then
        TodayDate = Format(Now, "YYYYMMDD")
        wbB.SaveAs Filename:= _
            pathName & TodayDate & " Backup.xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End If

and haven't encountered any issues so far. So in your case perhaps something like

'assign the dates to an array (your F2:AN2) If Not Weekday(yourDates(jji)) = 1 Then 'should work the same according to the documentation as comparing it to vbSunday 'your code here End If

It's also better to write an array in one go to your range (and reverse) instead of looping through a range and writing per iteration to your range.

To fit your mod-idea instead of dates, would this be something you're going for? Doesn't quite match yet with your first picture but it does the skipping properly. (getting more busy at work rn so will fix later if required)

Sub t1()
    Dim in1Days As Integer, in2Value As Integer, jj1 As Integer 'watch out with declaring your variables, "As Integer" has to be with every variable
    Dim os_New As Integer
    Dim skipD As Integer
    os_New = 0
    in1Days = Range("A3")
    in2Value = Range("B3")
    Dim arr()
    Dim maxCol As Integer
    maxCol = in1Days + in1Days \ 7 'in1Days \ 7 gives the division while discarding the rest
    ReDim arr(1 To 5, 1 To maxCol)
    
    For jj1 = 1 To in1Days
        arr(3, jj1 + skipD) = jj1 + skipD ' # Iteration
        
        'MOD CONDITION
        If (jj1 + skipD) Mod 7 = 0 Then
            skipD = skipD + 1
            arr(3, jj1 + skipD) = jj1 + skipD
        Else
            os_New = os_New + 1 'amount of cells that aren't a 7th day
        End If
        arr(1, jj1 + skipD) = in2Value
        arr(4, jj1 + skipD) = os_New
        arr(5, jj1 + skipD) = jj1 + skipD 'same as #iterations so kinda useless here
    Next jj1
    
    'POPULATE CELLS
    Range("F3").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 'write it in one go
End Sub

EDIT:

To skip the Saturday/Sunday (or 6th and 7th day depending on what you're going with):

Sub t1()
    Dim in1Days As Integer, in2Value As Integer, jj1 As Integer 'watch out with declaring your variables, "As Integer" has to be with every variable
    Dim os_New As Integer
    Dim skipD As Integer
    os_New = 0
    in1Days = Range("A3")
    in2Value = Range("B3")
    Dim arr()
    Dim maxCol As Integer
    maxCol = in1Days + 3 * (in1Days \ 6) 'in1Days \ 7 gives the division while discarding the rest
    ReDim arr(1 To 5, 1 To maxCol)
    
    For jj1 = 1 To in1Days
        arr(3, jj1 + skipD) = jj1 + skipD ' # Iteration
        
        'MOD CONDITION
        If (jj1 + skipD + 1) Mod 7 = 0 Then 'check if the weekend is about
            skipD = skipD + 1
            arr(3, jj1 + skipD) = jj1 + skipD
            skipD = skipD + 1
            arr(3, jj1 + skipD) = jj1 + skipD
        End If
        os_New = os_New + 1 'amount of cells that aren't a 7th day
        arr(1, jj1 + skipD) = in2Value
        arr(4, jj1 + skipD) = os_New
        arr(5, jj1 + skipD) = jj1 + skipD 'same as #iterations so kinda useless here
    Next jj1
    ReDim Preserve arr(1 To 5, 1 To jj1 - 1 + skipD)
    'POPULATE CELLS
    Range("F3").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 'write it in one go
End Sub

To use the Weekday() there really isn't much to change about the code. It only needs the startcode which is in C3 and since it has the value of 02/07/2024 which is a Tuesday, you'll notice a different outcome with 4 weekdays to start with (unlike the previous one where you started off with 5 days). I have tested with 40 days and had no error/issue.

Sub t1Weekday()
    Dim in1Days As Integer, in2Value As Integer, jj1 As Integer 'watch out with declaring your variables, "As Integer" has to be with every variable
    Dim os_New As Integer, skipD As Integer, sDate As Date
    os_New = 0
    in1Days = Range("A3")
    in2Value = Range("B3")
    sDate = Range("C3").Value
    Dim arr(), amt As Integer
    Dim maxCol As Integer
    maxCol = in1Days + 3 * (in1Days \ 6) 'in1Days \ 7 gives the division while discarding the rest
    ReDim arr(1 To 5, 1 To maxCol)
    
    For jj1 = 1 To in1Days
        arr(3, jj1 + skipD) = jj1 + skipD ' # Iteration
        
        'MOD CONDITION
        If Weekday(sDate + jj1 + skipD - 1) = 7 Then 'check if the weekend is about
        '-1 is needed since jj1 starts at 1, could also have taken 1 from sDate before the for loop
        ' Weekday() = 7 is for Saturday when the default start of the week is Sunday
            skipD = skipD + 1
            arr(3, jj1 + skipD) = jj1 + skipD
            skipD = skipD + 1
            arr(3, jj1 + skipD) = jj1 + skipD
        End If
        os_New = os_New + 1 'amount of cells that aren't a 7th day
        arr(1, jj1 + skipD) = in2Value
        arr(4, jj1 + skipD) = os_New
        arr(5, jj1 + skipD) = jj1 + skipD 'same as #iterations so kinda useless here
    Next jj1
    ReDim Preserve arr(1 To 5, 1 To jj1 - 1 + skipD)
    'POPULATE CELLS
    Range("F3").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 'write it in one go
End Sub

If you have any questions, feel free to ask

Answer 2

The code below assumed that the condition is something like this :
in1Days value = 17
in2Value value = 10
Put in2Value as many as in1Days, starting from cell F3 to the next column (same row) but skip every 7th cell

So the result of the sub (based on the example above) is like this:
F3:K3 value is 10 (there are six of 10), L3 blank, M3:R3 value is 10 (there are six of 10), S3 blank, T3:X3 value 10 (there are five of 10)... total 10 from F3:XFD3 is 17.

Another example:
in1Days value = 9
in2Value value = 10
So the result of the sub (based on the example above) is like this: F3:K3 value is 10 (there are six of 10), L3 blank, M3:O3 value is 10 (there are three of 10), total 10 from F3:XFD3 is 9.

Sub test()
Dim in1Days As Integer, in2Value As Integer, i As Integer, oFill As Range

Range("F3:XFD3").ClearContents
in1Days = 9 'Range("A3")
in2Value = 10 'range("B3")
Set oFill = Range("F3")
i = 1

Do
    If i Mod 7 <> 0 Then oFill.Value = in2Value
    If Application.CountA(Range("F3:XFD3")) = in1Days Then Exit Do
    Set oFill = oFill.Offset(0, 1): i = i + 1
Loop

End Sub

From the 2nd example,
the first until the 6th iteration will put value 10 into F3:K3. Then on the 7th iteration (when i = 7), because 7 mod 7 = 0, it just offset the oFill and add the i. So L3 is blank (the 7th iteration, i = 7). the countA in F3:XFD3 = 6, so it won't exit the Do. Have the oFill offset(0,1) and i + 1

Now the oFill is M3 and i = 8. Because 8 mod 7 <> 0 so it put 10 into oFill
Now the countA in F3:XFD3 = 7 <> in1Days, so it won't exit the Do.
Have the oFill offset(0,1) and i + 1

Now the oFill is N3 and i = 9. Because 9 mod 7 <> 0 so it put 10 into oFill
the countA in F3:XFD3 = 8 <> in1Days , so it won't exit the Do.
Have the oFill offset(0,1) and i + 1

Now the oFill is O3 and i = 10. Because 10 mod 7 <> 0 so it put 10 into oFill
the countA in F3:XFD3 = 9 = in1Days, so it exit the Do and exit the sub

Below is the sample result if in1Days value is 31

To test, just change in1Days and in2Value with different value.
But that if I understand you correctly.

---

To skip two cells after writing the in2Value to 5 cells....

Sub test2()
Dim in1Days As Integer, in2Value As Integer, i As Integer, oFill As Range

Range("F3:XFD3").ClearContents
in1Days = 27 'Range("A3")
in2Value = 10 'range("B3")
Set oFill = Range("F3")
f = 5: s = 2

Do
    For i = 1 To f
        oFill.Value = in2Value
        Set oFill = oFill.Offset(0, 1)
        If Application.CountA(Range("F3:XFD3")) = in1Days Then Exit Sub
    Next i
    Set oFill = oFill.Offset(0, s)
Loop

End Sub

This is another approach without using mod.
f variable is the how many cells to be filled, s variable is how many cells to be skipped.

Again, I'm very sorry as I still don't understand what you mean about weekday, nth day of the month, the connection of 02.07.2024 in cell C3 with the macro process to achieve the result. And 02.07.2024 in cell C3 is dd.mm.yyyy ? or is it mm.dd.yyyy ?

Please note, the two codes in my answer doesn't relate to any date/day/week of a certain month of a certain year at all. It's just a very simple command something like :

write in2Value as many as in1Days starting from cell F3 to the next column (same row) for N times (5 in the example code) then skip the column N times (2 in the example code) then continue writing to the next column if the countA in F3:XFD3 has not reach in1Days value.

Below is the result where in1Days = 31, f = 5 and s = 2.