Excel - adding a function, if necessary 1

[srogers]

On a worksheet I display the week number (periods are broken down into weeks - most have 4 but every once-in-a-while period 13 has 5 weeks). What I need to do before I fill that sheet with new data is look at that week number and decide what the next week number should be. That is easy for weeks 1, 2, and 3. When it comes to week 4 I have to look at another cell to see if it is period 13 or not. If not, then the week number is 1 (starting a new period). If the period is 13 it is possible there is a 5th week so I need to pop up some kind of box asking if there is, and based on a yes or no answer I'll decide what week it is (if yes then 5, if no then 1).

I started with a Select Case but when I tried to put an If Then statement into it there were problems - is it possible to do that? The If Then was in Case 4 and after there was Case 5 and when I tried to run it I got a message that said I had an End Select with no Select Case statement.

So that's why I thought I might need a function - I looked in Help and found one - but this is the first time I've tried a function and so I'm not sure if it is supposed to be separate (like another sub) and then I Call it or if I can put it inside.

Here's what I have:

Function NextWeek(WeekNumber, PeriodNumber)
Select Case WeekNumber
Case 1
WeekNumber = 2
MsgBox ("is this week right: " & WeekNumber)
Case 2
WeekNumber = 3
Case 3
WeekNumber = 4
Case 5
WeekNumber = 1
Case 4
If Worksheets("Forecast").Range("L3") <> 13 Then
WeekNumber = 1
Else
If Worksheets("Forecast").Range("L3") = 13 Then
Dim Msg, Style, Title, Response, MyString
Msg = "Will there be a 5th week in this period?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Decide if this is the 5th or 1st week" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
WeekNumber = 5
Else ' User chose No.

WeekNumber = 1
End If


End Select
End Function

Thank you.

 

[Chance1234]

needs to seperate then just type the name into the cell and use like any other function

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[srogers]

Oh, ok, so you create a function to use in a cell. Than maybe that's not what I want. I just need to determine the week number so I can copy the right range in from another workbook.

Can I use an IF Then inside a Select Case? I'd like to move Case 5 back down to the bottom - moving it up didn't help anyway.

Thank you -

 

[Chance1234]

oopss,
in excel you can reference a function from a cell, but you can also reference a function in VBA.


i think why your code isnt working is you are missing and end if

Function NextWeek(WeekNumber, PeriodNumber)
Select Case WeekNumber
    Case 1
        WeekNumber = 2
        MsgBox ("is this week right: " & WeekNumber)
    Case 2
        WeekNumber = 3
    Case 3
        WeekNumber = 4
    Case 5
        WeekNumber = 1
    Case 4
        If Worksheets("Forecast").Range("L3") <> 13 Then
        WeekNumber = 1
        Else
        If Worksheets("Forecast").Range("L3") = 13 Then
        Dim Msg, Style, Title, Response, MyString
        Msg = "Will there be a 5th week in this period?"    ' Define message.
        Style = vbYesNo + vbDefaultButton2     ' Define buttons.
        Title = "Decide if this is the 5th or 1st week"    ' Define title.

        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then    ' User chose Yes.
          WeekNumber = 5
         Else    ' User chose No.
        
        WeekNumber = 1
        End If
        end if 
    
End Select
End Function

Filmmaker, gentleman and i have a new site 3/4 working
[URL unfurl="true"]WWW.5YLAC.S5.COM[/URL]
[URL unfurl="true"]WWW.HOWTOBESPOOKY.CO.UK[/URL]

 

[Chance1234]

sorry thats two end ifs you are missing

Function NextWeek(WeekNumber, PeriodNumber)
Dim Msg, Style, Title, Response, MyString
Select Case WeekNumber
    Case 1
        WeekNumber = 2
        MsgBox ("is this week right: " & WeekNumber)
    Case 2
        WeekNumber = 3
    Case 3
        WeekNumber = 4
    Case 5
        WeekNumber = 1
    Case 4
        If Worksheets("Forecast").Range("L3") <> 13 Then
            WeekNumber = 1
        Else
        
            If Worksheets("Forecast").Range("L3") = 13 Then
        
                Msg = "Will there be a 5th week in this period?"    ' Define message.
                Style = vbYesNo + vbDefaultButton2     ' Define buttons.
                Title = "Decide if this is the 5th or 1st week"    ' Define title.

                Response = MsgBox(Msg, Style, Title)
                
                If Response = vbYes Then    ' User chose Yes.
                    WeekNumber = 5
                Else    ' User chose No.
        
                WeekNumber = 1
                End If
        End If
        End If
        
    
End Select
End Function

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Chance1234]

also you can test functions in VBA by hitting ctrl+g to open the debug window

and typing a ? followed by the function name and arguments for the function so for the above

?nextweek(1,1)



Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[srogers]

Oh I sure did - thank you. And thanks for the note on testing functions!

Sharon