vba to find specific combination of chars in one column

[gint32]

Hi all
I have a column that contains free text, I need a way (using vba) to find specific chars in this column the format of the text chars will always be like .. one Alpha and 7 numeric(together).....Examples of this is are D1564567, A1235567, all the way through to Z2356457 and if and when found copy this alpha numeric to he adjacent cell on the right. Not all cells will have this so it needs to e able to skip over records that do not contain, any help appreciated

 

[gint32]

Great stuff.. (RX!)... thanks for the comments ..didnt realise vba do those routines..i thought arrays was as far as it could go ..

 

[Peter_SSs]

You're welcome.

 

[gint32]

For this issue, double-click ThisWorkbook and paste the code below into the right hand pane that just opened.

Pete is there any way to automate this instead of pasting into this workbook, reasons being is a lot of users will not be able to do the right click and insert code, the thought of that would probably scare them to death...

 

[Peter_SSs]

Pete is there any way to automate this instead of pasting into this workbook, reasons being is a lot of users will not be able to do the right click and insert code, the thought of that would probably scare them to death...

Where is the Find_Matches macro housed and how are users activating it?

 

[gint32]

Some using a keyboard shortcut..and or an icon on the quick ribbon...code is located in there personal.xlsb ..within C: users username xlstart thanks..the code get copied from a network location to their personal folder via a button in a ms word that I created for ease .this is the only way i know of to run vba.

 

[Peter_SSs]

Some using a keyboard shortcut..and or an icon on the quick ribbon...code is located in there personal.xlsb

Then could this work for you? Instead of the double-click, put this code in a similar location to the Find_Matches code & have the users simply select a cell in column A or B of the last (new) sheet and then run this code in a similar way to the previous code.

Sub Show_Detail()
  Dim rng As Range, c As Range
  Dim txt As String
 
  If ActiveSheet.Index = Sheets.Count Then
    If ActiveCell.Column <= 2 Then
      Set rng = ActiveCell.EntireRow.Resize(, 2)
      With Sheets("Workbook 2")
        .AutoFilterMode = False
        With .Range("A1:F" & .Range("A" & .Rows.Count).End(xlUp).Row)
          .AutoFilter Field:=1, Criteria1:=rng.Cells(1).Value, Operator:=xlOr, Criteria2:=rng.Cells(2).Value
          .AutoFilter Field:=6, Criteria1:="*" & rng.Cells(1).Value & "*", Operator:=xlOr, Criteria2:="*" & rng.Cells(2).Value & "*"
          For Each c In .Columns(1).SpecialCells(xlVisible)
            If c.Row > 1 Then txt = txt & vbLf & vbLf & c.Value & ":" & vbLf & c.Offset(, 5).Value
          Next c
        End With
        .AutoFilterMode = False
      End With
      If Len(txt) Then MsgBox txt
    End If
  End If
End Sub

 

[gint32]

Thanks Pete..will try it out at work tonight and get back to you..

 

[gint32]

yep, work as stated..thanks Pete

 

[Peter_SSs]

yep, work as stated..thanks Pete

Good news!
Thanks for the follow-up.