Need help creating VBA code from an existing array formula...

[Lidsavr]

I am running Excel 2007.

I am writing code and have come up against a situation that I cannot figure out. I have searched the board and cannot find a similar situation in which to draw information.

In VBA, I want to extract and keep the numerals from a string (stripping off the alpha characters). For instance, If my string is: ABC145Z. Then I want the formula (vba) to return the numeric part, 145.


I am not sure how to do this in VBA. I found a "formula" in the Excel® 2007 Bible (J. Walkenback, p.342); however, the book does not deal with VBA.

This is the formula shown in the book:

{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT
(“1:”&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR
(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1)))}

Can anyone help me with the VBA equivalent of this formula?

Thank you,

Charles

References:
John Walkenbach, (2007), Excel® 2007 Bible, Wiley Publishing, Inc., Indianapolis, Indiana 46256

 

[kpark91]

What about A12345Z6

should it return 123456?

 

[Lidsavr]

kpark91,
Great question. It is one example that I did not think about.

The answer is no. I only want it to return the first set of numbers.

Thank you for asking!

Charles

 

[kpark91]

Hi, use this code (put it in a public moduel)

Function getNumber(ByRef Target As Range) As String
    If Target.Count > 1 Then Exit Function
    Dim i&, sw As Byte
    getNumber = ""
    sw = 0
 
    For i = 1 To Len(Target.Value)
        If IsNumeric(Mid(Target.Value, i, 1)) Then
            getNumber = getNumber & Mid(Target.Value, i, 1)
            sw = 1
        ElseIf sw = 1 Then
            Exit Function
        End If
    Next i
End Function

To use it. Use it like

=getNumber(A1)

 

[Lidsavr]

kpark91,
It is not working, but I am sure it is because I am doing something wrong. The value I want to remove the numbers from is in cell K1. I am placing the number in cell K2.

After putting the code in its own Function routine, I placed the following after selecting cell K2.

            Range("K2").Select
            =getNumber(K1)

I get an error message when I attempt to execute the code that says: "Expected Line Number or label or statement or end of statement>"

If I remove the = sign, the code now looks like this:

            Range("K2").Select
            getNumber (K1)

and I get an "Object Required" error.

Can you tell me what am I doing wrong?

Thanks,

Charles
P.S. I'm sure it is obvious that I do not use Functions often, so it is a new area for me. (Learn, Learn, Learn!)

 

[kpark91]

Hi, using functions can be tricky at first :P

Here are the steps to make it work:
1) you need to go to VBE by pressing Alt + F11.
2) View > Project Explorer (Ctrl + R) [It might already be showing]
3) Right-click in your project file found in the explorer
4) Insert > Module
5) Double-click the module newly created in the explorer.
6) Copy + Paste the given code from this site into the newly created module.

So, now you have finished setting up by copy + pasting the code into the editor.
Now, to use the code... You must follow these steps:
1) Go To worksheet
2) In a cell, put =getNumber(K1)

You can use it just like a formula.

OR

if you want to use it like a code, you have gotten the right idea but not the syntax.
Use and change the red words to your worksheet name you're working with

Worksheets("Sheet1").Range("K2").Value = getNumber(Worksheets("Sheet1").Range("K1"))

The reason why your code wasn't working is because the computer doesn't recognize "K1" by itself. You must make it a range object since the function is only recognizing the range object.
To make "K1" a range object, you can simply add Range()

Range("K1")