Getting a number from the end of a string
 

I'm working on a macro which takes the raw data from an instrument
work with and performs calculations on it and does some formattin
work.

My latest quest has been to take a cell populated like this - "Posito
Y: 1.28" - and pull out the number for further use. I was able t
create an Excel function which works perfectly for this
Code
-------------------
=VALUE(MID(A17,12,LEN(A17)-10)
-------------------
Basically, it pulls all characters from 12 on (the first numerical one
and formats it as a number using VALUE().

Where I'm running into trouble is converting this function for VBA use
I tried using the below line, but without luck
Code
-------------------
DeltaYSpacing = Application.WorksheetFunction.Value(Application.Wo rksheetFunction.Mid(Range("A17"), 12, Application.WorksheetFunction.Len(Range("A17")) - 10)
-------------------
DeltaYSpacing is defined prior to this line as a Double.

The problem I'm getting is this
Code
-------------------
Run-time error '438':

Object doesn't support this property or metho
-------------------
From what I can tell by the drop-down boxes that come up while typin
that line in, Mid() and Len() aren't available for use.

I know I could just put the original formula into a cell then read tha
cell's value for further use, but that's more work than should be neede
for this. Is there a way to perform this function with VBA

--
Message posted from http://www.ExcelForum.com

Getting a number from the end of a string
 

Hi
try
DeltaYSpacing =
cdbl(Mid(Range("A17").value,12,Len(Range("A17").va lue) - 10))

--
Regards
Frank Kabel
Frankfurt, Germany


I'm working on a macro which takes the raw data from an instrument I
work with and performs calculations on it and does some formatting
work.

My latest quest has been to take a cell populated like this -
"Positon
Y: 1.28" - and pull out the number for further use. I was able to
create an Excel function which works perfectly for this:
Code:
--------------------
=VALUE(MID(A17,12,LEN(A17)-10))
--------------------
Basically, it pulls all characters from 12 on (the first numerical
one)
and formats it as a number using VALUE().

Where I'm running into trouble is converting this function for VBA
use.
I tried using the below line, but without luck.
Code:
--------------------
DeltaYSpacing =

Application.WorksheetFunction.Value(Application.Wo rksheetFunction.Mid(R
ange("A17"),
12, Application.WorksheetFunction.Len(Range("A17")) - 10))
--------------------
DeltaYSpacing is defined prior to this line as a Double.

The problem I'm getting is this:
Code:
--------------------
Run-time error '438':

Object doesn't support this property or method
--------------------
From what I can tell by the drop-down boxes that come up while typing
that line in, Mid() and Len() aren't available for use.

I know I could just put the original formula into a cell then read
that
cell's value for further use, but that's more work than should be
needed
for this. Is there a way to perform this function with VBA?


---
Message posted from http://www.ExcelForum.com/

Getting a number from the end of a string
 

Perfect!

I love you guys! :

--
Message posted from http://www.ExcelForum.com