ChristineJ
Well-known Member
- Joined
- May 18, 2009
- Messages
- 768
- Office Version
- 365
- Platform
- Windows
I use the macro below to look at the formula in a cell and return all cell references in the string that are "not allowed" in another cell
In the code below, allowed = "G5,G6,G7,G8,E15,E16,E17,E21,E24,E25,G18,G26," --- so a formula of =G8+L15-E25*A1 in one cell would return L15, A1 in another cell. (They are then wrapped in a SUM function.)
Is there a way to convert this from a macro to a function, such as =HelperCells(B15), where B15 contains the formula? Thanks!
In the code below, allowed = "G5,G6,G7,G8,E15,E16,E17,E21,E24,E25,G18,G26," --- so a formula of =G8+L15-E25*A1 in one cell would return L15, A1 in another cell. (They are then wrapped in a SUM function.)
Is there a way to convert this from a macro to a function, such as =HelperCells(B15), where B15 contains the formula? Thanks!
Code:
Sub HelperCells(fCell As String, rCell As String) '
Dim xRegEx As Object, xMatch As Object, d As Object
Dim allowed As String, Sheetname As String, s As String
Dim addresses As String
Dim c As Integer
Dim r As Integer
Dim n As Integer
Dim addaray As Variant
allowed = "G5,G6,G7,G8,E15,E16,E17,E21,E24,E25,G18,G26,"
Sheetname = ActiveSheet.Name & "!"
Set d = CreateObject("Scripting.Dictionary")
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
For Each xMatch In .Execute(Replace(Replace(Replace(Replace(Range(fCell).Formula, ":", ","), "$", ""), " ", ""), Sheetname, ""))
If InStr(1, "," & allowed & ",", "," & xMatch & ",") = 0 And Not d.exists(CStr(xMatch)) Then
s = s & ", " & xMatch
d(CStr(xMatch)) = 1
End If
Next xMatch
End With
addresses = Mid(s, 3)
If addresses = "" Then
addresses = addresses
Else
addresses = "=SUM(" & Mid(s, 2) & ")"
End If
Range(rCell).Value = addresses
addaray = Split(addresses, ",")
End Sub
