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]

Peter..Thanks ever so much

 

[Peter_SSs]

Peter..Thanks ever so much

You're welcome.

(I hope you don't have any fights at the event. :wink: )

 

[gint32]

Unavoidable really on this line of work..but at least we can minimise. ??

 

[Peter_SSs]

Unavoidable really on this line of work..but at least we can minimise. ����

 

[gint32]

post the adjustment

<del>d(a(i, 1) & ";" & m) = Empty</del>
If Not d.exists(a(i, 1) & ";" & m) And Not d.exists(m & ";" & a(i, 1)) Then d(a(i, 1) & ";" & m) = Empty

Hi Pete,
If you get the time, would you mind commenting the code, so I might try and figure out a little of what and how you managed to solve this.

Also, my next task is the double clicking to jump to the that related free text record.

I guess something such as setting a variable for the cell value A & B in the results (new sheet), with a Goto or Find, I think the main issue is I'll need a find based on each pair thats selected (right-clicked or highlighted) I think!

 

[gint32]

Sorry, but I have just discovered the following
that if the free text contains the same Id# in Col A & Col B

example:

Workbook 2.Column A .value C0841196

Workbook 2.Column F contains..C0841196


Then I get the following output in a new sheet”

Col A Col B
C0841196 C0841196

 

[Peter_SSs]

Sorry, but I have just discovered the following

Then I get the following output in a new sheet”

Col A Col B
C0841196 C0841196

To resolve this issue, add the blue code.

If Not d.exists(a(i, 1) & ";" & m) And Not d.exists(m & ";" & a(i, 1)) And m <> a(i, 1) Then d(a(i, 1) & ";" & m) = Empty

 

[Peter_SSs]

Also, my next task is the double clicking to jump to the that related free text record.

I guess something such as setting a variable for the cell value A & B in the results (new sheet), with a Goto or Find, I think the main issue is I'll need a find based on each pair thats selected (right-clicked or highlighted) I think!

For this issue, try the following code. In the vba window, find your project in the left hand pane, double-click ThisWorkbook and paste the code below into the right hand pane that just opened.

Then after you have run the code that creates the new sheet, go to that new sheet and double click any value in col A or col B. You should be taken back to Workbook 2 and be presented with that data filtered to only show the row(s) that contain the combination of values that you double clicked on the new sheet (though I haven't tested this greatly). If required, you can manually remove the AutoFilter altogether from Workbook 2, or unfilter columns A & F, or we could have other code unfilter that sheet.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Dim rng As Range
  
  If Sh.Index = Sheets.Count Then
    If Target.Column <= 2 Then
      Cancel = True
      Set rng = Target.EntireRow.Resize(, 2)
      Application.ScreenUpdating = False
      With Sheets("Workbook 2")
        .Activate
        .AutoFilterMode = False
        With .Range("A1:F" & .Range("A" & .Rows.Count).End(xlUp).Row)
          .AutoFilter Field:=1, Criteria1:="*" & rng.Cells(1).Value & "*"
          .AutoFilter Field:=6, Criteria1:="*" & rng.Cells(2).Value & "*"
        End With
      End With
      Application.ScreenUpdating = True
    End If
  End If
End Sub

 

[gint32]

For this issue, try the following code. In the vba window, find your project in the left hand pane, double-click ThisWorkbook and paste the code below into the right hand pane that just opened.

MsgBox Field:=6, Criteria1:="*" & rng.Cells(2).Value & "*"

Thanks Pete work great! one last point is there a way to msgbox the whole cell value of the cell as in :

MsgBox Field:=6, Criteria1:="*" & rng.Cells(2).Value & "*"/Code]

meaning display all of the free text,  if not thats great and thanks for the good work :)

 

[gint32]

To resolve this issue, add the blue code.

 And m <> a(i, 1)

Superb..You fixed my issue.