| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value# in VBA script with reference
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value# in VBA script with reference
Sounds like you are trying to assign a string to a long variable. Check all
of those cells that they contain numeric data. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value# in VBA script with reference
Hi Theo,
Try this: dim wks as Excel.Worksheet set wks = Thisworkbook.Worksheets("wegingsfactoren") Function CalcValue(pVal) As Variant If pVal = 1 and pVal <=18 Then CalcValue = wks.Range("D" & pVal + 2).value else CalcValue = 0 end if end function Sub Test() Dim i as integer Dim t as variant for i = -1 to 20 t = CalcValue(i) debug.print t next i end sub This will quickly tell you if you have a problem on a cell having anything other than a long integer and will test your function for the boundary conditions. I didn't really need to re-write your function other than to change the return type to variant for the test, but I couldn't resist it!!! Let me know how you go... Regards, Ric |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value# in VBA script with reference
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA script | Excel Discussion (Misc queries) | |||
| HOWTO Reference a named cell or range in a script | Excel Discussion (Misc queries) | |||
| Help with the script | Excel Discussion (Misc queries) | |||
| Script to locate unique reference help please | Excel Programming | |||
| Excel 2000/XP script to Excel97 script | Excel Programming | |||
Linear Mode
