String Creating Function

[Nike6106]

Hi

I currently use this formula in my spreadsheet
="ED"&VLOOKUP(MONTH(N107),immcodes,2,FALSE)&RIGHT(YEAR(N107),1)

"ED" would be my futhandle and N107 is my futdate
immcodes is a named range of 2 column array that looks like this

1 F
2 G
3 H
4 J
5 k
6 M
7 N
8 Q
9 U
10 V
11 X
12 Z

I have written the following function, but cant even get the first message box to appear...any ideas?

Function mazfutcodeoffdate(futhandle, futdate As Date, lookuptable, colreturn)


futmonthx = WorksheetFunction.Month(futdate)
ans = MsgBox("test", vbOKCancel)




futfullyear = WorksheetFunction.Year(futdate)
futyear = WorksheetFunction.Right(futfullyear, 1)

futmonthcode = WorksheetFunction.VLookup(futmonth, lookuptable, lcolreturn, False)



mazfutcodeoffdate = futhandle & futmonthcode & futyear


End Function

[romperstomper]

I have written the following function, but cant even get the first message box to appear...any ideas?

So what do you get in the cell where you are using this?

[Nike6106]

I should get something like EDH2

But the function is returning #VALUE

[JeanRage]

Hi,

See attached sample UDF ...

HTH

[romperstomper]

That implies an error in the UDF or in how you are calling it. Can you post a sample?