Type Mismatch within UDF

[adj87]

I have 2 UDFs which are suppose to work in tandem. 1 finds the holidays for a given year (HolidayLookup), then calculates their week number and assigns them specific prorated values for the type of holiday (like for the week of Christmas, your goal is 30% less). This part works great ironically despite being far more complicated.

When I try to tie this into the simple part of then going through each week, and applying this prorated value to a specific calculation our company does to determine weekly goals, I get the mismatch error.

I have built this out as follows:

Function RunLine(StartNumber As Variant, EndNumber As Variant, CurrentQuarter As Variant, CurrentYear As Variant) As Variant


Dim Wk1 As Variant
...
Dim Wk52 As Varint

Wk1 = [(EndNumber - StartNumber) / 51 * 0 + StartNumber] * HolidayLookup(CurrentYear, 1)
...
Wk52 = [(EndNumber - StartNumber) / 51 * 51 + StartNumber] * HolidayLookup(CurrentYear, 52)

Select Case CurrentQuarter
    Case "1"
        RunLine = Wk1 + Wk2 + Wk3 + Wk4 + Wk5 + Wk6 + Wk7 + Wk8 + Wk9 + Wk10 + Wk11 + Wk12 + Wk13
    Case "2"
        RunLine = Wk14 + Wk15 + Wk16 + Wk17 + Wk18 + Wk19 + Wk20 + Wk21 + Wk22 + Wk23 + Wk24 + Wk25 + Wk26
    Case "3"
        RunLine = Wk27 + Wk27 + Wk29 + Wk30 + Wk31 + Wk32 + Wk33 + Wk34 + Wk35 + Wk36 + Wk37 + Wk38 + Wk39
    Case "4"
        RunLine = Wk40 + Wk41 + Wk42 + Wk43 + Wk44 + Wk45 + Wk46 + Wk47 + Wk48 + Wk49 + Wk50 + Wk51 + Wk52
End Select

End Function

I know Variants aren't the best data types to use, but I have tried using what the actual variable calls for (Long/Integer), and the similar problems occur. I have also tried to have 1 giant UDF which combines both these calculations, but after a lot of testing, it became easier to test them separately when I was getting the proper results from the holiday part of the calculation.

Any help on this would be greatly appreciated because I am very stuck. This part just seems so simple, but it will not work. Also, if the code for the other UDF is required to help root out the issue, please let me know!

Windows 7 64-bit, Excel 2016, i5-4210U, 4GB RAM

 

[NdNoviceHlp]

I'm guessing your wk1 or wk52 errors then gives a type mismatch error in your case statement. Place this line of code above your select case to see if U have values. HTH. Dave

 Msgbox "wk1  " &  wk1  & "  wk52  " & wk52

 

[adj87]

Thanks for following up Dave. This actually lead me to the solution and I can't believe I did this! "[]" define arrays, and I just used them to help visually separate the math. If I replace them with "()", everything works out! I was right, just a random mistake.

 

[NdNoviceHlp]

You are Welcome!. Glad U got it sorted out. Thanks for posting your outcome. Dave