# Use function code in macro

#### ChristineJ

##### Well-known Member
This function extracts the cell references from a string and returns them in another cell.
Example: D10 contains =A4+B23+100-SUM(K12:L15)
G10 contains =ExtractCellRefs(D10) and that returns A4, B23, K12:L15

Is it possible to convert this to a macro, where every cell in column D with a value has its cell references (or "No Matches") returned on the same row in column G?

Code:
``````Function ExtractCellRefs(Rg As Range) As String
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String

Application.Volatile
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\\$?[A-Z]{1,3}\\$?[0-9]{1,7}(:\\$?[A-Z]{1,3}\\$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
ExtractCellRefs = Left(xRet, Len(xRet) - 2)
Else
ExtractCellRefs = "No Matches"
End If
End Function``````

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Snakehips

##### Well-known Member
@ChristineJ
Try this.
VBA Code:
``````Sub ExtractCellRefs()
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String

Application.ScreenUpdating = False
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\\$?[A-Z]{1,3}\\$?[0-9]{1,7}(:\\$?[A-Z]{1,3}\\$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With

For Each Rg In Range("D3:D" & Range("D" & Rows.Count).End(xlUp).row)

Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
Rg.Offset(0, 3) = Left(xRet, Len(xRet) - 2)
Else
Rg.Offset(0, 3) = "No Matches"
End If
xRet = ""
Next Rg

Application.ScreenUpdating = True
End Sub``````

Hope that helps

#### ChristineJ

##### Well-known Member
@ChristineJ
Try this.
VBA Code:
``````Sub ExtractCellRefs()
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String

Application.ScreenUpdating = False
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\\$?[A-Z]{1,3}\\$?[0-9]{1,7}(:\\$?[A-Z]{1,3}\\$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With

For Each Rg In Range("D3:D" & Range("D" & Rows.Count).End(xlUp).row)

Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
Rg.Offset(0, 3) = Left(xRet, Len(xRet) - 2)
Else
Rg.Offset(0, 3) = "No Matches"
End If
xRet = ""
Next Rg

Application.ScreenUpdating = True
End Sub``````

Hope that helps
Brilliant! This works perfectly. Thanks so much for your quick reply. C

Replies
11
Views
898
Replies
6
Views
260
Replies
19
Views
426
Replies
17
Views
422
Replies
3
Views
365

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,120
Messages
5,768,227
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back