Getting a number from the end of a string

  • Thread starter Thread starter RyanVM
  • Start date Start date
R

RyanVM

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.WorksheetFunction.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
 
Hi
try
DeltaYSpacing =
cdbl(Mid(Range("A17").value,12,Len(Range("A17").value) - 10))
 
Back
Top