formula validation

J

jim

I would like to be able to check to see if a formula has been modified. I
need to do this for a lot of cells, so I am trying to use a loop, but I am
having no luck. Any ideas?
 
T

Tom Ogilvy

How can you tell the formula has been modified?

Dim rng as Range, cell as Range
set rng = Selection.SpecialCells(xlFormulas)
for each cell in rng
if somecondition then
msgbox cell.Address & " has been modified"
End if
Next
 
C

Chip Pearson

Jim,

How would you test whether a formula has been modified? Do you
have an original formula store somewhere, against which you would
compare the current formula?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
C

chris

if the cells are all in a set area and are contiguous then do thi
dim Rng as Range, C as Rang
Set Rng = Range(Cells(R1, C1), Cells(R2, C2))
'where R1 is beginng row # of first cell, R2 is ending row # for last cel
'C1 is beginning Column # for first cell, C2 is ending Column # for last cell

For each C in Rng
"Code for validating cell content
Next

----- jim wrote: ----

I would like to be able to check to see if a formula has been modified.
need to do this for a lot of cells, so I am trying to use a loop, but I a
having no luck. Any ideas
 
J

jim

I have the formula in the code. I need to be able to reference the formula
in the cell and compare it to a string containing the formula. Each cell
has a different formula. I was tring to us a case for the cell like this.

formularange = Worksheets("Testing Sheet").Range("e8: f25 )

For Each cell In formularange



For Each cell In formularange

Select Case cell

Case e8
formulatest = "=E9+C8"
Case e9
formulatest = "=E10+C9"
End Select

If cell.Formula = formulatest Then
Response = MsgBox(cell.Formula & vbNewLine & formulatest)
Else
Response = MsgBox(cell.Formula & vbNewLine & formulatest)
End If

The message boxes were just for testing purposes, but the problem is with
the if statement.
 
C

Chip Pearson

Jim

You want to use the Address property to determine which cell you
are working with in the For Each loop. For example,

For Each cell In FormulaRange
Select Case Cell.Address(False,False)
Case "E8"
formulatest = "=E9+C8"
Case "E9"
' etc
End Select
If cell.Formula = formulatest Then
' same formula
Else
' different formula
End If
Next cell


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
D

David Prout

I think you will need to give spme more info. What are original formulas, in what wat are they
expected to alter, where are they etc

DavidP
 
T

Tom Ogilvy

If your purpose is to restore the formula, why waste time checking if the
formula was modified or not. Just rewrite the formula.
 
J

jim

Here is the code that gives me that error.

Select Case cell.Address(False, False)

The reason we need to verify the formula is to check for tampering because
it has happened in the past.

Thanks to everyone with their help so far. It is greatly appreciated.
 
J

jim

I think I got it. I had to use

For Each c In Worksheets("Testing Sheet").Range("e8: f25 , e26, c27")

Select Case c.Address

Case "$E$8"
formulatest = "=E9+C8"
Case "$E$9"
formulatest = "=E10+C9"

Thanks for your help and comments.
 
Top