formula validation
 

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?

formula validation
 

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

formula validation
 

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.

"jim" wrote in message
...
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?

formula validation
 

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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?

formula validation
 

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

On Tue, 13 Apr 2004 14:17:53 -0500, "jim" wrote:

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?

formula validation
 

I get runtime error 424
object required
with that case statement.


"Chip Pearson" wrote in message
...
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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?

formula validation
 

What line of code is causing the error. Post your code.


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


"jim" wrote in message
...
I get runtime error 424
object required
with that case statement.


"Chip Pearson" wrote in message
...
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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?

formula validation
 

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

--
Regards,
Tom Ogilvy

"jim" wrote in message
...
I get runtime error 424
object required
with that case statement.


"Chip Pearson" wrote in message
...
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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?

formula validation
 

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.
"Chip Pearson" wrote in message
...
What line of code is causing the error. Post your code.


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


"jim" wrote in message
...
I get runtime error 424
object required
with that case statement.


"Chip Pearson" wrote in message
...
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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?

formula validation
 

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.

"jim" wrote in message
...
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.
"Chip Pearson" wrote in message
...
What line of code is causing the error. Post your code.


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


"jim" wrote in message
...
I get runtime error 424
object required
with that case statement.


"Chip Pearson" wrote in message
...
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


"jim" wrote in message
...
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.

"jim" wrote in message
...
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?