VBA Optimization

[markusreyes2907]

Hi, recently I wrote some code which works perfectly. I've only been doing VBA for about a month, so wondering if anyone out there can take a look at it and let me know if there's a way simpler way to do achieve the same goal. The code I created is only a small part of a whole, so the user would enter a number of experimental days in an InputBox which would I would have those days 1 to whatever the number entered in most of the other sections. However, in this section of my tables I need the numbers by a factor of 7. If the number of experimental days is less than 7 then you would have Day 0 and END. If it's a multiple of it 7 you would have 0, 7, 14...etc without END. If its any other number between the multiples of the 7 (lets say the user entered 25 as number of experimental days) it would look like 0, 7, 14, 21 and END. I hope that makes sense.

Anyway just some curiosity! Below is the code. Thanks!

With Cells
    .Font.Name = "Times New Roman"
    .Font.Size = 10
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With

Dim numdays&, i As Integer, j As Integer, k As Integer
numdays = InputBox("Number of days", "Days")
k = (numdays + 7) \ 7
    For j = 0 To 7 Step 7
        For i = 1 To k
            Range("B4").Cells(1, i) = j
    j = j + 7
    Next i
Next j


If numdays = 7 Then
    k = numdays \ 7
    Range("B4").Resize(, k + 1).Borders(3).Weight = xlThin: Range("B4").Resize(, k + 1).Borders(4).Weight = xlThin
ElseIf numdays = (7 * k) - 7 Then
    Range("B4").Resize(, k).Borders(3).Weight = xlThin: Range("B4").Resize(, k).Borders(4).Weight = xlThin
ElseIf numdays < 7 Then
    Range("B4").Resize(, k + 1).Borders(3).Weight = xlThin: Range("B4").Resize(, k + 1).Borders(4).Weight = xlThin
    Range("B4").Offset(, 1) = "END"
Else
    Range("B4").Resize(, k + 1).Borders(3).Weight = xlThin: Range("B4").Resize(, k + 1).Borders(4).Weight = xlThin
    Range("B4").End(xlToRight).Offset(, 1) = "END"
End If

 

[Norie]

You don't need both loops and you could use a different method to get the values.

Dim numdays&, i As Integer, j As Integer, k As Integer
Dim x As Variant

    With Cells
        .Font.Name = "Times New Roman"
        .Font.Size = 10
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With


    numdays = InputBox("Number of days", "Days")

    k = (numdays + 7) \ 7

    x = Application.Transpose(Evaluate("(ROW(1:" & k & ")-1)*7"))

    If numdays Mod 7 <> 0 Then
        ReDim Preserve x(k)
        x(k) = "END"
    Else
        k = k - 1
    End If
    
    With Range("B4").Resize(, k + 1)
        .Borders(3).Weight = xlThin
        .Borders(4).Weight = xlThin
        .Value = x
    End With

 

[markusreyes2907]

You don't need both loops and you could use a different method to get the values.

Dim numdays&, i As Integer, j As Integer, k As Integer
Dim x As Variant

    With Cells
        .Font.Name = "Times New Roman"
        .Font.Size = 10
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With


    numdays = InputBox("Number of days", "Days")

    k = (numdays + 7) \ 7

    x = Application.Transpose(Evaluate("(ROW(1:" & k & ")-1)*7"))

    If numdays Mod 7 <> 0 Then
        ReDim Preserve x(k)
        x(k) = "END"
    Else
        k = k - 1
    End If
   
    With Range("B4").Resize(, k + 1)
        .Borders(3).Weight = xlThin
        .Borders(4).Weight = xlThin
        .Value = x
    End With

Oh great thank you for the repose! I’ve seen Evaluate used in code but haven’t quite gotten a grasp on it. I see how useful it is and now I’ll just do some more studying to figure it out. Really appreciate it!