Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2022, 07:51 AM
Matt C's Avatar
Matt C Matt C is offline Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Windows 10 Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Office 97-2003
Advanced Beginner
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro
 
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Question Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro

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
Attached Files
File Type: xls Budget Spreadsheet - VALUE errors.xls (64.5 KB, 5 views)
Reply With Quote
  #2  
Old 01-07-2022, 12:44 PM
p45cal's Avatar
p45cal p45cal is offline Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Windows 10 Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

A quick fix might be to include
Application.Volatile
as the first line of the function.
Reply With Quote
  #3  
Old 01-08-2022, 06:03 AM
Matt C's Avatar
Matt C Matt C is offline Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Windows 10 Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Office 97-2003
Advanced Beginner
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro
 
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Default

Hi. Thanks for this. It half works.

I've added the line as you suggested:

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

Application.Volatile

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
The "Insert Row" macro (see code in first post) now seems to work fine, but I'm still getting the error after running the "Delete Row" macro (code below).

Quote:
Sub DeleteRow()
'
' DeleteRow Macro
'
response = MsgBox("Are you sure you want to remove this row?", vbYesNo)

If response = vbYes Then

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

Else
Exit Sub

End If

End Sub
As before, deleting a row manually does not result in the "#VALUE!" error.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft