VBA Code to check formulas in a range
 

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to search
for the answer myself, however, I am hoping one of you gurus can give me a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1). This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

Make sure that D1 reads =SUM(A1:C1). Hover the mouse over the bottom right
corner of the cell (D1) so that it becomes a black cross. Click and drag down
to the bottom of your data. This will put the correct formula in each cell.

Hope this is what you were after
Rowan

"FrigidDigit" wrote:

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to search
for the answer myself, however, I am hoping one of you gurus can give me a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1). This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

One way:

Instead of checking each formula, it's probably just as easy to re-enter
the formulae:

With Sheets("Sheet1")
.Cells(1, 4).Resize(.Cells(.Rows.Count, _
1).End(xlUp).Row).Formula = "=SUM(A1:C1)"
End With


In article ,
"FrigidDigit" wrote:

I have a looming deadline for this spreadsheet else I'd try harder to search
for the answer myself, however, I am hoping one of you gurus can give me a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1). This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each cell,
taking into account the row offset change?

Thanks for any advice!

VBA Code to check formulas in a range
 

If you just want to check each cell via VBA then something like

Sub chk()
Dim eRow As Long
Dim myRange As Range
Dim cell As Range

eRow = Cells(Rows.Count, 3).End(xlUp).Row
Set myRange = Range(Cells(1, 4), Cells(eRow, 4))
For Each cell In myRange
If cell.FormulaR1C1 < "=SUM(RC[-3]:RC[-1])" Then
MsgBox "Error in cell " & cell.Address
End If
Next cell

End Sub

Regards
Rowan

"FrigidDigit" wrote:

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to search
for the answer myself, however, I am hoping one of you gurus can give me a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1). This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

Thanks Rowan, but I was looking for VBA code to do this.
Ciao!

"Rowan" wrote in message
...
Make sure that D1 reads =SUM(A1:C1). Hover the mouse over the bottom right
corner of the cell (D1) so that it becomes a black cross. Click and drag
down
to the bottom of your data. This will put the correct formula in each
cell.

Hope this is what you were after
Rowan

"FrigidDigit" wrote:

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to
search
for the answer myself, however, I am hoping one of you gurus can give me
a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a
user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1).
This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each
cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

Thanks, but I need to point out to the endusers which formulas they
overwrite (if any). Your code will certainly come in handy for other
projects tho!
FD

"JE McGimpsey" wrote in message
...
One way:

Instead of checking each formula, it's probably just as easy to re-enter
the formulae:

With Sheets("Sheet1")
.Cells(1, 4).Resize(.Cells(.Rows.Count, _
1).End(xlUp).Row).Formula = "=SUM(A1:C1)"
End With


In article ,
"FrigidDigit" wrote:

I have a looming deadline for this spreadsheet else I'd try harder to
search
for the answer myself, however, I am hoping one of you gurus can give me
a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a
user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1).
This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each
cell,
taking into account the row offset change?

Thanks for any advice!

VBA Code to check formulas in a range
 

Thanks Rowan!
Exactly what I was looking for!

FD

"Rowan" wrote in message
...
If you just want to check each cell via VBA then something like

Sub chk()
Dim eRow As Long
Dim myRange As Range
Dim cell As Range

eRow = Cells(Rows.Count, 3).End(xlUp).Row
Set myRange = Range(Cells(1, 4), Cells(eRow, 4))
For Each cell In myRange
If cell.FormulaR1C1 < "=SUM(RC[-3]:RC[-1])" Then
MsgBox "Error in cell " & cell.Address
End If
Next cell

End Sub

Regards
Rowan

"FrigidDigit" wrote:

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to
search
for the answer myself, however, I am hoping one of you gurus can give me
a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a
user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1).
This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each
cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

Just another question:
If I need to use cell references (not R1C1) can I still do something
similar?
Then I could use this with some of my more complicated formulas?

Thanks

FD


"FrigidDigit" wrote in message
...
Thanks Rowan!
Exactly what I was looking for!

FD

"Rowan" wrote in message
...
If you just want to check each cell via VBA then something like

Sub chk()
Dim eRow As Long
Dim myRange As Range
Dim cell As Range

eRow = Cells(Rows.Count, 3).End(xlUp).Row
Set myRange = Range(Cells(1, 4), Cells(eRow, 4))
For Each cell In myRange
If cell.FormulaR1C1 < "=SUM(RC[-3]:RC[-1])" Then
MsgBox "Error in cell " & cell.Address
End If
Next cell

End Sub

Regards
Rowan

"FrigidDigit" wrote:

Hi All,

I have a looming deadline for this spreadsheet else I'd try harder to
search
for the answer myself, however, I am hoping one of you gurus can give me
a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a
user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1).
This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each
cell,
taking into account the row offset change?

Thanks for any advice!

Frigid Digit

VBA Code to check formulas in a range
 

Why not lock those cells and protect the sheet?

"FrigidDigit" wrote:

Thanks, but I need to point out to the endusers which formulas they
overwrite (if any). Your code will certainly come in handy for other
projects tho!
FD

"JE McGimpsey" wrote in message
...
One way:

Instead of checking each formula, it's probably just as easy to re-enter
the formulae:

With Sheets("Sheet1")
.Cells(1, 4).Resize(.Cells(.Rows.Count, _
1).End(xlUp).Row).Formula = "=SUM(A1:C1)"
End With


In article ,
"FrigidDigit" wrote:

I have a looming deadline for this spreadsheet else I'd try harder to
search
for the answer myself, however, I am hoping one of you gurus can give me
a
quick answer:

I wish to check that the formulae in a sheet have not been changed by a
user
and that each cell contains the correct formula.

I have values in columns A:C and in D I have the formula =Sum(A1:C1).
This
continues down for about 250 rows.

How can I check down the D column that the formula is correct in each
cell,
taking into account the row offset change?

Thanks for any advice!