Change hard coded cell references to variables in code

[ChristineJ]

The code below works in looking at a formula in a cell (P13 in this case) and returning a statement (in cell CH13 in this case) if cells were used in the formula that were not in the allowable range.

I'd like to see if there is a way to make two changes:

1. Rather than hard code the P13 and CH13, I'd like to be able those cell references variables so I can call the HelperCells macro and enter in the cells there, such as Call HelperCells("P13", "CH13")

2. The statement returned in cell CH13 is "Helper cell(s) should not have been referenced in the formula." if cells that are not allowed were used. Could that statement be written to include the values of those cells, such as "Helper cell(s) A1, J1 should not have been referenced in the formula." (if A1 and J1 were used in the formula in cell P13).

Thanks!

Sub HelperCells()
  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 = "D9,E9,D15,D16,F15,H15,I15,D22,D23," & _
"F22,H22,I22,D27,D28,D32,D33,E37," & _
"E38,E39,E40,F37,F38,F39,F40," & _
"F44,F45,F46,F47,F48" & _
"P10,P11,P12,P13,P14,P15,P16"

  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(Range("P13").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
   End If
   
   If addresses <> "" Then
    addresses = "Helper cell(s) should not have been referenced in the formula."
  End If  

  Range("CH13").Value = addresses
addaray = Split(addresses, ",")
End Sub

 

[Peter_SSs]

. Rather than hard code the P13

I note that P13 is hard-coded in both the 'allowed' string and in the For Each xMatch line of code. Are you only referring to removing the hard-coding from the For each xMatch line?

 

[Peter_SSs]

Are you only referring to removing the hard-coding from the For each xMatch line?

Assuming Yes, try this

Sub Test()
  Helper_Cells "P13", "CH13"
End Sub

Sub Helper_Cells(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 = "D9,E9,D15,D16,F15,H15,I15,D22,D23," & _
"F22,H22,I22,D27,D28,D32,D33,E37," & _
"E38,E39,E40,F37,F38,F39,F40," & _
"F44,F45,F46,F47,F48" & _
"P10,P11,P12,P13,P14,P15,P16"

  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(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
   End If
   
   If addresses <> "" Then
    addresses = "Helper cell(s)" & Mid(s, 2) & " should not have been referenced in the formula."
  End If

  Range(rCell).Value = addresses
addaray = Split(addresses, ",")
End Sub

 

[ChristineJ]

Assuming Yes, try this

Sub Test()
  Helper_Cells "P13", "CH13"
End Sub

Sub Helper_Cells(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 = "D9,E9,D15,D16,F15,H15,I15,D22,D23," & _
"F22,H22,I22,D27,D28,D32,D33,E37," & _
"E38,E39,E40,F37,F38,F39,F40," & _
"F44,F45,F46,F47,F48" & _
"P10,P11,P12,P13,P14,P15,P16"

  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(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
   End If
  
   If addresses <> "" Then
    addresses = "Helper cell(s)" & Mid(s, 2) & " should not have been referenced in the formula."
  End If

  Range(rCell).Value = addresses
addaray = Split(addresses, ",")
End Sub

Absolutely perfect! And yes, the P13 should remain as is in the "allowed" string. I did not think to say that.

Thank you for help on this and for all the other times you have assisted me with questions. Christine

 

[Peter_SSs]

You're welcome once again.