Split alpha from alphanumeric then use in macro

[stuartf]

Hi,

I am trying to create a macro that extracts the alpha prefix from a string in cell P2 then use the result to run a specific sub routine based on the outcome.

the strings are always alpha first ie VPT12345 or CEMS2468 however the alpha length varies

What I am wanting to do is if the result of the extraction = VPT then run a specific sub routine, or CEMS then run a different sub routine.

At the moment I am using using a User prompt to determine which sub routine to run I would like to simplify this process.

I am using Excel 2010 on Windows 7

Any help would be great

Cheers

 

[Rick Rothstein]

Consider this code....

Sub GetPrefixFromP2()
  Dim X As Long, TextInP2 As String, Prefix As String
  TextInP2 = Range("P2").Value
  For X = 1 To Len(TextInP2)
    If Mid(TextInP2, X, 1) Like "#" Then
      Prefix = Left(TextInP2, X - 1)
      Exit For
    End If
  Next
  '
  '  The Prefix variable now contains the letter
  '  prefix so you can use it here however you like
  '
End Sub

 

[Leith Ross]

Hello stuartf,

Here is another macro that will allow both upper and lowercase letters. The Select Case statement easily allows you to match the prefix that will call the macro for it. Replace MAcro_X and MAcro_Y in the code with the correct macro name to call.

Sub Macro1A()

  Dim Alpha As String
  Dim RegExp As Object
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
      Set Wks = ActiveSheet
      Set Rng = Wks.Range("P2")
      
          Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
          If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
          
          Set RegExp = CreateObject("VBScript.RegExp")
          RegExp.Pattern = "^([A-Za-z]+)(.*)"
          
              For Each Cell In Rng
                
                Alpha = RegExp.Replace(Cell, "$1")
                
                Select Case Alpha
                    Case Is = "VPT": Call Macro_X
                    Case Is = "CEMS": Call Marco_Y
                End Select
                
              Next Cell
              
End Sub

 

[stuartf]

Hi Rick and Leith,

Will try both options,

Thanks for that

Stuart

 

[stuartf]

Hi Rick and Leith,

thank you both for your suggestions, I have used Rick's for the moment as we are only checking the first cell, I will however build a macros using Leith's suggestion as we are bound to change our minds and check each cell in the range.

So once again thank you very much as my query is now resolved.

Cheers
Stuart

 

[Rick Rothstein]

I have used Rick's for the moment as we are only checking the first cell, I will however build a macros using Leith's suggestion as we are bound to change our minds and check each cell in the range.

Here is an approach you may want to consider. The following function will return and array of all the prefixes in the column whose address you pass into the function. First, here is the function...

Function Prefixes(Addr As String) As Variant
  Dim X As Long, LowerBound As Long, Rng As Range, Copy As Variant, Temp As Variant
  Set Rng = Range(Addr)
  If Rng.Columns.Count > 1 Then
    Prefixes = Array()
    Exit Function
  End If
  Copy = Rng
  Application.ScreenUpdating = False
  For X = 0 To 9
    Rng.Replace X, "", xlPart
  Next
  If Rng.Count = 1 Then
    LowerBound = LBound(Array(1, 2))
    ReDim Temp(LowerBound To LowerBound)
    Temp(LowerBound) = Rng.Value
    Prefixes = Temp
  Else
    Prefixes = WorksheetFunction.Transpose(Rng)
  End If
  Rng = Copy
  Application.ScreenUpdating = True
End Function

You can use this for one or more cells in a column. Here is a sample of its use...

Sub Test()
  Dim X As Long, A As Variant
  A = Prefixes("P5:P205")
  For X = LBound(A) To UBound(A)
    Debug.Print A(X)
  Next
End Sub

Note that if you pass in an address string for a range consisting of more than one column, an empty range is returned (the LBound is 0 and the UBound is -1). This function always returns an array, so if you pass in a single cell address, a one-dimensional array will be returned, so you will have to retrieve its value with A(LBound(A)) where A is the assumed array name and the use of LBound protects against systems where the Option Base has been set to 1. Okay, so after you assign this function call to a variant variable, you can iterate it (the order of the elements will be the same as the order of the values in the range represented by the address you passed in) and do your coding directly against its array elements. Because of the way I code the function, this should be faster than visiting each cell in the range one cell at a time.

 

[stuartf]

Hi Rick,

Thanks for that, I will give it a try.


Cheers
Stuart