Parsing a formula: qualify sheet for all range references

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
hmm... i think my suggestion (example) above doesn't make sense... :banghead:

Probably i misunderstood what you need

M.
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
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
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
Windows
hmm... i think my suggestion (example) above doesn't make sense... :banghead:

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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
Windows
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!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,613
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top