VBA code to locate number pattern and add url link.

[johmarr]

Hi,

I'm pretty stuck and no doubt I'll explain this wrong...

I'm trying to write a vba scrpt to locate a number pattern that is 3 digits - 7 digits - 7 digits. When found I want to make it a url by adding a web address before it and then adding the original cell value at the end of the url.

For example:

If the cell value was "123-4567890-1234567" then the VBA would hyperlink the cell value with the url "http://www.testurl.com/123-4567890-1234567"

Here is my current code:

Range("A2").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value like "***-*******-*******" Then
ActiveCell.Offset(0, 1).Value = "http://www.testurl.com/" & ActiveCell.Offset(0, 0)
Else
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
MsgBox ("There is an incorrect Order Number. Please review manually")
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Hyperlinks.Add ActiveCell, ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
Loop
Columns("B:B").Delete
​

Unfortunately the wildcard *'s I put in does not help if there are invalid Order Numbers.


I tried regex but my VBA knowledge isnt great and got stuck trying to implement it into my script.

​

Any ideas?

 

[Leith Ross]

Hello johmarr,

Replace the asterisks with pound sign. The pound sign is any single digit from 0 to 9.

If ActiveCell.Value like "###-#######-#######" Then