In addition to Bob's advice (maybe change "As Long" to "As Variant")

If pVal = 1 Then
CalcValue = wegingsfactoren!D3

CalcValue = Range("wegingsfactoren!D3").value

or
Dim sAddr as string

If pVal = 1 Then
sAddr = "D3"

etc

CalcValue =Worksheets("wegingsfactoren").range(sAddr)

or
sAddr = "wegingsfactoren!D3"
etc
CalcValue =Range(sAddr)

==================

do you need a UDF, eg
=IF(AND(A10,A1<19),INDIRECT("wegingsfactoren!D" & A1 + 2),0)

Regards,
Peter T



"TMJ Vermeer" wrote in message
ups.com...
Hi,

I'm not a specialist in VBA, but also not a newbee (I think).
I try to get the contence of a cell from a different worksheet but I
get the message: VALUE#
(A value used in the formula is of the wrong data type). What am I
doing wrong!
Below the function.

Function CalcValue(pVal) As Long

If pVal = 1 Then
CalcValue = wegingsfactoren!D3

ElseIf pVal = 2 Then
CalcValue = wegingsfactoren!D4

ElseIf pVal = 3 Then
CalcValue = wegingsfactoren!D5

ElseIf pVal = 4 Then
CalcValue = wegingsfactoren!D6

ElseIf pVal = 5 Then
CalcValue = wegingsfactoren!D7

ElseIf pVal = 6 Then
CalcValue = wegingsfactoren!D8

ElseIf pVal = 7 Then
CalcValue = wegingsfactoren!D9

ElseIf pVal = 8 Then
CalcValue = wegingsfactoren!D10

ElseIf pVal = 9 Then
CalcValue = wegingsfactoren!D11

ElseIf pVal = 10 Then
CalcValue = wegingsfactoren!D12

ElseIf pVal = 11 Then
CalcValue = wegingsfactoren!D13

ElseIf pVal = 12 Then
CalcValue = wegingsfactoren!D14

ElseIf pVal = 13 Then
CalcValue = wegingsfactoren!D15

ElseIf pVal = 14 Then
CalcValue = wegingsfactoren!D16

ElseIf pVal = 15 Then
CalcValue = wegingsfactoren!D17

ElseIf pVal = 16 Then
CalcValue = wegingsfactoren!D18

ElseIf pVal = 17 Then
CalcValue = wegingsfactoren!D19

ElseIf pVal = 18 Then
CalcValue = wegingsfactoren!D20

Else
CalcValue = 0
End If

End Function

Thanks

Theo Vermeer