Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a number from the end of a string
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of Occurrences of a String Value within a String | Excel Discussion (Misc queries) | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
counting the number of instances of a string within another string | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
converting number string to number with decimal | Excel Worksheet Functions |