Hi folks
I'm looking for some help with a budget spreadsheet which (hopefully!) is attached to this message.
I have a handful of Functions set up in the VBA to calculate cells with specific Style names. For example:
Quote:
Function SumCat(WorkRng As Range)
'
' SumCat Function
'
' Calculates cells formatted as "Total Category"
' Example: =SUMCAT(A1:C9)
'
Dim rng As Range
Dim xSum As Long
For Each rng In WorkRng
' Finds cells with "Total Category" stylename
If rng.Style = "Total Category" Then
xSum = xSum + rng.Value
End If
Next
SumCat = xSum
End Function
|
Secondly, I have a couple of macros which speed up the insert/delete row process. Here's one:
Quote:
Sub InsertRow()
'
'
Application.ScreenUpdating = False
response = MsgBox("Insert a line here?", vbYesNo)
If response = vbNo Then
MsgBox "No Lines will be Inserted"
Else
' ActiveSheet.Unprotect
' Insert Identical Row
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Clear Line Item Description
ActiveCell.Offset(1, 1).Select
ActiveCell.ClearContents
' Clear Amnt
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "1"
' Clear Units
ActiveCell.Offset(0, 1).Select
ActiveCell.ClearContents
' Clear "X"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "1"
' Clear Rate
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
' Clear Working
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "0"
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
' Back to Line Item
ActiveCell.Offset(0, -7).Select
End If
Application.ScreenUpdating = True
End Sub
|
When I use the "Insert Row" or "Delete Row" macros (which both work absolutely fine), I get a "#VALUE!" error in any cells containing my custom Functions, e.g. "SUMCAT" (but not in any cells which contain the usual "SUM"). However, the problem does
not occur when inserting or deleting rows manually - the "SUMCAT" cells retain their calculation correctly.
I'd be grateful if someone could have a look at the stripped down spreadsheet I've attached to illustrate the issue and see if they can shed any light on what's happening. I've added a few notes at the top and bottom of the document.
Thanks! Matt