Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro

Matt C · #1 01-07-2022, 07:51 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional function to operate a formula based on "Yes" or "No" Condition	Des ONeill	Excel	1	07-03-2021 12:17 AM
Condtional function to operate a formula based on "Yes" or "No"	Des ONeill	Excel	3	07-03-2021 12:16 AM
How do I delete the names under "Exceptions" when I use the "Restrict Editing" feature in Word?	MengS	Word	0	02-25-2015 02:57 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM