Link to home
Start Free TrialLog in
Avatar of lblinc
lblincFlag for United States of America

asked on

vb 6.0 --> In vb code, VLookup using Application.WorksheetFunction.VLookup() returning 82.00 instead of 82.327, 75.00 instead of 75.728 .. etc..

In vb 6, I'm doing a VLookup which references an excel range...    using Application.WorksheetFunction.VLookup(parmInvestName, rngFind, 1, False)  , but it keeps returning the whole # without the decimal..    but i need the decimal.

This is urgent question as I'm not very familiar with vb syntax..   i've tried changing from Double to Long and everything else, but it still doesn't return the decimal places for a double type..  here is example of one of my attempts  :  

Public Function FindPutDeltaRefSecond(parmInvestName As String) As Long
    Dim rngFind As Range
    Set rngFind = RefData.Option.TablePutData
    On Error GoTo Terminate
    FindPutDeltaRefSecond = CLng(Application.WorksheetFunction.VLookup(parmInvestName, rngFind, 3, False))
Terminate:
    Set rngFind = Nothing
End Function
Avatar of lblinc
lblinc
Flag of United States of America image

ASKER

I tried using different combinations using   CDbl, CLng, and then trying to Format(x, "#00.00") the Double as a string..  still nothing but zeroes to the right of decimal is returned.    In the spreadsheet, the numbers I'm doing the VLookup to retrieve look like this ...  

90.662
82.327
75.728
69.252
48.399
36.910
30.355
17.092

Instead I keep getting this returned in VLookup..

90.000
82.000
75.000
69.000
..
..






ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lblinc

ASKER

Yean I know what you're saying, i've tried everything..     i thought maybe for some reason the input retrieved from spreadsheet possibly was read in as a string..    I've tried multiple combinations to try to get the correct data when debugging..
Avatar of lblinc

ASKER

Finally got it, thanks for a bit of input chris..
For the record, what was the issue ... it will help others in the future?

Chris