Parsing a formula: qualify sheet for all range references

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
Hi All

I have a very large workbook with a large volume of validation rules across multiple sheets. The validation uses custom hence standard formulas that evaluate to either TRUE/FALSE.

I would like to extract all of the formulas in to a single worksheet. In doing so I find a problem in that most validations are within-sheet and hence the range references are not qualified to worksheet. E.g.: Formula1: =A1=100

I cannot drop this formula to a single sheet because it needs to evaluate A1 in the sheet that contains the validation rule.

Is there a crafty way to qualify all range references? Or do I need to go through the route of splitting the formula string by a variety of delimiters and check whether or each element can evaluate to a range or not?

Thanks.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Jon

Not sure i understand exactly what you need. My suggestion is to use something like this
Cells(x,y) = Sheets("SheetName").Evaluate(StringFormula)

An example

Sheet1

A
B
C
1
Values​
Formulas​
2
100​
110​
3
90​
100​
4
110​
120​

<tbody>
</tbody>


Formula in C2 copied down
=A2+10

Sheet2

A
B
C
1
Values​
Formula​
2
100​
200​
3
50​
100​
4
40​
80​

<tbody>
</tbody>


Formula in C2 copied down
=A2*2

Sheet Summary before macro

A
B
C
D
1
Sheets/Cells​
C2​
C3​
C4​
2
Sheet1​
3
Sheet2​

<tbody>
</tbody>


Code:
Sub aTest()
    Dim rCell As Range, ws1 As Worksheet
    Dim ws2 As Worksheet, strFormula As String, i As Long
    
    Set ws1 = Sheets("Summary")
    
    For Each rCell In ws1.Range("A2:A3")
        Set ws2 = Sheets(rCell.Value)
        For i = 1 To 3
            strFormula = ws2.Range(ws1.Cells(1, rCell.Offset(, i).Column)).Formula
            rCell.Offset(, i).Value = ws2.Evaluate(strFormula)
        Next i
    Next rCell

End Sub

After macro...


A
B
C
D
1
Sheets/Cells​
C2​
C3​
C4​
2
Sheet1​
110​
100​
120​
3
Sheet2​
200​
100​
80​

<tbody>
</tbody>


M.
 
Upvote 0
hmm... i think my suggestion (example) above doesn't make sense... :oops:

Probably i misunderstood what you need

M.
 
Last edited:
Upvote 0
Hi Jon. Nice to hear from you.

Instead of writing or copy paste the formula you can cut paste the formula.
In this case I believe you'll get the worksheet reference.

My idea is: use an intermediate cell in each worksheet.
You can write the validation formula in that cell and then cut paste that cell into the target worksheet.


For ex.:
Assuming:
- you'll use cell ZZ1 as intermediate in each worksheet
- you have a worksheet called Validations where you'll store the validation formulas.

This is an example:
You have your validation in Sheet1!C1.
1 - write the Sheet1!C1 validation formula in Sheet1!ZZ1
2 - cut paste Sheet1!ZZ1 to Validations!A2

If the validation formula in Sheet1!C1 is =A1=100, you'll get in Validations!A2 the formula: =Sheet1!A1=100

Code:
Sub Test()
Dim wsh1 As Worksheet, wsh2 As Worksheet

Set wsh1 = Worksheets("Sheet1")
Set wsh2 = Worksheets("Validations")

' Copy the validation formula in Sheet1!C1 to Validations!A2 (use Sheet1!ZZ1 as intermediate)
With wsh1
    .Range("ZZ1").Formula = .Range("C1").Validation.Formula1
    .Range("ZZ1").Cut wsh2.Range("A2")
End With

End Sub

HTH
 
Upvote 0
hmm... i think my suggestion (example) above doesn't make sense... :oops:

Probably i misunderstood what you need

M.
Hi Marcelo

Thanks though for having a crack. Still gives food for thought because Worksheet.Evaluate could indeed have offered some for of workaround.
 
Upvote 0
Hi Jon. Nice to hear from you.

Instead of writing or copy paste the formula you can cut paste the formula.
In this case I believe you'll get the worksheet reference.
Wonderful - thank you Pedro! Very cunning workaround and it seems to be working a treat!
 
Upvote 0
Hi Marcelo

Thanks though for having a crack. Still gives food for thought because Worksheet.Evaluate could indeed have offered some for of workaround.

Yes, i thought Worksheet.Evaluate could be a workaround. My idea was to create a table like...


A
B
C
D
1
SheetName​
Cell​
Formula as text​
Result​
2
Sheet1​
B1​
=A1=100​
TRUE​
3
Sheet1​
B2​
=A2=100​
FALSE​

<tbody>
</tbody>


and a function

Code:
Function EvalForm(shName As String, strForm As String)
    EvalForm = Sheets(shName).Evaluate(strForm)
End Function

in D2 copied down
=EvalForm(A2,C2)

But Pedro (pgc01) has provided a much better solution (y)

M.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top