Excel VBA to Loop

[sirmacademy]

Hello everyone, I need to make this code run as one. Thank you for your help.
Sub InsertSaturday()
Dim i As Integer
i = 6
Do While i < 100
Cells(i, 3).Value = "Saturday"
i = i + 6
Loop
End Sub


Sub InsertSunday()
Dim j As Integer
j = 7
Do While j < 100
Cells(j, 3).Value = "Sunday"
j = j + 7
Loop
End Sub

 

[pike]

Hello,
you can combine both like,,,

Sub InsertSaturdaySunday()
 Dim i As Integer
 i = 6
 Do While i < 100
 Cells(i, 3).Value = "Saturday"
 Cells(i+1, 3).Value = "Sunday" ' just add one
 i = i + 6
 Loop
 End Sub

 

[dmt32]

Hi,
taking both your codes, following combined version should produce same result for you

Sub InsertSaturdayAndSunday()
    Dim i As Integer, j As Integer
    i = 6: j = 7
    Do While i < 100
        Cells(i, 3).Value = "Saturday"
        If j < 100 Then Cells(j, 3).Value = "Sunday"
        i = i + 6: j = j + 7
    Loop
End Sub

Dave

 

[Rick Rothstein]

Hello,
you can combine both like,,,

Sub InsertSaturdaySunday()
 Dim i As Integer
 i = 6
 Do While i < 100
 Cells(i, 3).Value = "Saturday"
 Cells(i+1, 3).Value = "Sunday" ' just add one
 i = i + 6
 Loop
 End Sub

You can write this macro a little more compactly like this...

[table="width: 500"]
[tr]
	[td]Sub InsertSaturdaySunday()
  Dim i As Integer
  For i = 6 To 100 Step 7
    Cells(i, 3).Resize(2).Value = [{"Saturday";"Sunday"}]
  Next
End Sub[/td]
[/tr]
[/table]

This function can also be written with using a loop...

[table="width: 500"]
[tr]
	[td]Sub InsertSaturdayAndSunday2()
  Range("C6:C99") = Evaluate("IF(MOD(ROW(6:99)-6,7)=0,""Saturday"",IF(MOD(ROW(6:99)-6,7)=1,""Sunday"",C6:C99))")
End Sub[/td]
[/tr]
[/table]

 

[sirmacademy]

Thank you very much, Sir. You have made it properly the code.

 

[sirmacademy]

This code works very fine.
Sub InsertSaturdayAndSunday2() Range("C6:C99") = Evaluate("IF(MOD(ROW(6:99)-6,7)=0,""Saturday"",IF(MOD(ROW(6:99)-6,7)=1,""Sunday"",C6:C99))")End Sub

But it should be Saturday and Sunday will be inserted and it will not replace the value.

 

[Rick Rothstein]

But it should be Saturday and Sunday will be inserted and it will not replace the value.

Does this macro do what you want...

Sub InsertSaturdaySunday()
  Dim i As Integer
  For i = 6 To 100 Step 7
    Cells(i, 3).Resize(2).Insert xlToRight
    Cells(i, 3).Resize(2).Value = [{"Saturday";"Sunday"}]
  Next
End Sub