Auto-filling a Series

[forgameslive]

Hi,


I am trying to automatically get the following series filled in but have failed thus far:


1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)


Similarly,


Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?


Any help would be much appreciated!


Thanks!

[JeffreyWeir]

How far do you need the series to extend? You can achieve the 'skipping dates' thing by using custom lists. See http://office.microsoft.com/en-nz/ex...010222142.aspx

But i don't think it will work for your number sequence question. Although you can use a formula.
Put this in row 1 and drag down:
=INT(ROW()/7-1/7)*10+IF(MOD(ROW(),7)=0,7,MOD(ROW(),7))
Then you could copy and paste special as values.

[NoS]

You could achieve this with macros.

For the numbers something like this

Sub Skip890()
    Dim rng As Range
    Dim cel As Range
    Dim cNum As Long
    
    'where ever you are putting the numbers
    Set rng = Range("C3:C38")
    
    'put the number you want to start at in cell A2
    cNum = Range("A2").Value
        
    For Each cel In rng
        cel.Value = cNum
        cNum = cNum + 1
        If Right(cNum, 1) = 8 Then
            cNum = cNum + 3
        End If
    Next cel
    
End Sub

maybe like this for the days

Sub SkipFriToSun()
    Dim rng As Range
    Dim cel As Range
    Dim cDa As Date
    
    'where ever you are putting the days
    Set rng = Range("D3:D38")
    
    'put the starting date in cell B2
    cDa = Range("B2").Value
    
    For Each cel In rng
        'if the day is Friday add 3 days to get Monday
        If Format(cDa, "ddd") = "Fri" Then
            cDa = cDa + 3
        End If
        
        'format this how ever you want it displayed
        cel.Value = Format(cDa, "Long Date")
        'get ready for next day
        cDa = cDa + 1
    Next cel
    
End Sub

I put this code into the sheets module, hope it's of some use, just an idea.

[JeffreyWeir]

Here's a formula that handles any series of repeating numbers on the far right from 1 to 9. Just replace X with the number
=INT((ROW()-1)/x)*10+MOD(ROW(),-x)+x