CODEWITHSUNDEEP
vbaexcel
user2726121
user2726121

Reputation: 1

How to find if cell contains specific characters in specific positions?

I'm trying to build a macro that will search a given cell for a certain character combination in a specific order, and then paste this to another page. I need it to search a cell that contains a 9 letter/number combination for '9P', but only if it's the third and fourth number/letter in.

So if given these list of entries:

NA9PK99LJ
NA9PK99LK
XX9P109LH
XX9P109XF
XX849P01D
NA8419PZ3
XX9P109VK

I'd only want it to copy the first four and the last entry, and then past these vertically starting in A2 on another page.

I'm somewhat of a novice to excel vba, but I'm told this should be doable.

Any help would be much appreciated!

Thanks

Chris

Upvotes: 0

Views: 7440

Answers (2)

Rob White
Rob White

Reputation: 1002

Hope this helps, may have gone over board

Sub Solution()

Dim search As String, start As Integer, lastaddress As String, toworksheet As String

lastaddress = "A2" 'cell location on the result sheet
search = InputBox("Enter Search Critera") 'enter search critera
start = InputBox("Start from") 'integer of where to search in the string, not zero index
toworksheet = InputBox("Put results into which spreadsheet") 'worksheet name to put results

'select the cell you want to start your search from and it will continue till it reaches a    blank cell
Do While ActiveCell.Text <> ""

'Performs the test
If Mid(ActiveCell.Text, start, Len(search)) = search Then

'adds the entry to the results sheet
Worksheets(toworksheet).Cells.Range(lastaddress).Value = ActiveCell.Text
'updates the address to the next line in your results sheet
lastaddress = Worksheets(toworksheet).Cells.Range(lastaddress).Offset(1, 0).Address
End If

'goes to next item in list
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Upvotes: 1

ApplePie
ApplePie

Reputation: 8942

The easiest way I know of is to use Find() with wildcards. What you can do is use the macro recorder while doing the following:

  • Start the macro recorder
  • Select the range to search
  • Do Ctrl + F and enter ??9P????? into the search terms
  • Select all the results in the box
  • Close the box, copy the cells and paste them where you want
  • Stop the macro recorder

This will give you the main base of your code which you can then fine tune so that it does what you need. This is also a good exercice for you to learn how VBA works.

Upvotes: 0

Related Questions