Parsing a formula: qualify sheet for all range references

[Jon von der Heyden]

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.

 

[Marcelo Branco]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Values​

[/TD]
[TD][/TD]
[TD]

Formulas​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD]

110​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

90​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

110​

[/TD]
[TD][/TD]
[TD]

120​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=A2+10

Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Values​

[/TD]
[TD][/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD]

200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD]

80​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=A2*2

Sheet Summary before macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Sheets/Cells​

[/TD]
[TD]

C2​

[/TD]
[TD]

C3​

[/TD]
[TD]

C4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Sheet1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Sheet2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Sheets/Cells​

[/TD]
[TD]

C2​

[/TD]
[TD]

C3​

[/TD]
[TD]

C4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Sheet1​

[/TD]
[TD]

110​

[/TD]
[TD]

100​

[/TD]
[TD]

120​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Sheet2​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD]

80​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[Marcelo Branco]

hmm... i think my suggestion (example) above doesn't make sense...

Probably i misunderstood what you need

M.

 

[pgc01]

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

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

 

[Jon von der Heyden]

hmm... i think my suggestion (example) above doesn't make sense...

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.

 

[Jon von der Heyden]

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!

 

[pgc01]

I'm glad it helped. Cheers!

 

[Marcelo Branco]

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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

SheetName​

[/TD]
[TD]

Cell​

[/TD]
[TD]

Formula as text​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Sheet1​

[/TD]
[TD]

B1​

[/TD]
[TD]

=A1=100​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Sheet1​

[/TD]
[TD]

B2​

[/TD]
[TD]

=A2=100​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]


and a function

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

M.