Numbering (1 Viewer)

[Valentine]

I want to put the numbers 1 - 7 accross a single row in an excel spreadsheet using VBA.
I also want to put Monday - Sunday repeating through a single row till the end of my columns.
i have column D to ND formatted
If I start the coding

With xlSh.Range("D2", "ND2")

I am at a loss as to how to add in the for loop to add the numbers and week days.

 

[Valentine]

    With xlSh.Range("D2", "AH2")
        .HorizontalAlignment = xlVAlignCenter
        For Each cell In xlSh.Range("D2:AH2").Cells
            counter = (counter + 1)
            .Value = counter
        Next
    End With

This puts the total of counter in all the cells instead of just 1, 2, 3, 4, etc. what am i doing wrong?

 

[Gasman]

Walk through the code
I would have thought you would need cell.value ?

 

[The_Doc_Man]

Yes, Gasman is right. The For loop steps through cells but doesn't establish a new WITH context, so the .Value in that code would be evaluated as

xlSh.Range("D2","AH2").Value = counter

which surely isn't what you meant. Leaving .Value uses the last explicit WITH context, because if it used any of the implied contexts, there would be far too many.

 

[arnelgp]

create a function:

Public Function FillWithValue(ByRef rng As Range, Optional ByVal FillWithDate As Boolean = False)
    Dim r As Range
    Dim j As Integer
    j = 1
    For Each r In rng
        If FillWithDate Then
            r = WeekdayName(j, False, vbSunday)
        Else
            r = j
        End If
        j = j + 1
        If j > 7 Then
            j = 1
        End If
    Next
End Function

then on your code:

'for number 1 to 7
Call FillWithValue(xlSh.Range("D2:AH2"))

'for Sunday to Saturday value
Call FillWithValue(xlSh.Range("D2:AH2"), True)

 

[Darrell]

Try something like this..

Sub Macro1()
Dim i As Integer
Dim y As Integer

With ThisWorkbook.Sheets("Sheet5")
    .Range("D3") = "Monday"
    .Range("D3").AutoFill Destination:=.Range("D3:ND3"), Type:=xlFillDefault
    
    y = 4
    i = 1
    Do Until .Cells(2, y).Address = "$NE$2"
        .Cells(2, y) = i
        If i = 7 Then
            i = 1
        Else
            i = i + 1
        End If
        y = y + 1
    Loop
    
    .Columns("D:ND").HorizontalAlignment = xlCenter
End With

End Sub