Val function with contents of a Hyperlinked cell

[Neal Zimm]

Hi All,
Why does a cell with a text value fail the Val function?

Background:
c1 holds 44 1st Sub Row as its value AND is hyperlinked to a cell in row
44.
Rec is a user defined record and .FirSubRow is a long field.


With Ws 'stepping thru the code the Rec value was 44.
' line below did not work as expected
If Rec.FirSubRow <> Val(.Range("c1").Value) Then

'Code here is working fine
End If
End With


This is code I put in front of the above to isolate the number leading the
string:

sHoldAy = split(LTrim(.Range("c1").Value), " ")
Lnum = Val(sHoldAy(0))

and, If Rec.FirSubRow <> Lnum then .... worked. How come ?

The ornery thing about this, is that I have other code,
almost the same as the not-working line above, that DOES
WORK as expected, hmmm.... Make a function to do the split?

Thanks,
Neal

 

[Jim Cone]

Neal,
The Val function returns 441 from "44 1st Sub Row".
Is that what you expected?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Neal Zimm"
wrote in message
Hi All,
Why does a cell with a text value fail the Val function?

Background:
c1 holds 44 1st Sub Row as its value AND is hyperlinked to a cell in row
44.
Rec is a user defined record and .FirSubRow is a long field.
With Ws 'stepping thru the code the Rec value was 44.
' line below did not work as expected
If Rec.FirSubRow <> Val(.Range("c1").Value) Then

'Code here is working fine
End If
End With
This is code I put in front of the above to isolate the number leading the string:

sHoldAy = split(LTrim(.Range("c1").Value), " ")
Lnum = Val(sHoldAy(0))

and, If Rec.FirSubRow <> Lnum then .... worked. How come ?
The ornery thing about this, is that I have other code,
almost the same as the not-working line above, that DOES
WORK as expected, hmmm.... Make a function to do the split?
Thanks,
Neal