Solved

Cannot create new row or paste data into new sheet, VB Error and Large action error

Posted on 2011-09-08
3
240 Views
Last Modified: 2012-05-12
I have and excel sheet that i use for my accounts and payment from my clients, as part of this sheet i have a part payment button that supposed to deduct a given amount from a clients total bill, crerate a new row below with the new amount minus the part payment, however i get an error syaing the operastion is large. I have tryed this on a i7 machine with 8GB of RAM, so it is not a resource issue. I have attached screenshots of the errors.

The error appear when i click the part pay button which runs VB code, it has worked untill now and i am wondering why it has broke all of a sudden, i cannot even manually create a new row.

IO have trieed copying the data into a new sheet and the errors persist.

 error 1
 error 1
 error 1

error-2.PNG
error-3.PNG
Invoice-log.xlsm
0
Comment
Question by:UplandSystems
3 Comments
 
LVL 7

Accepted Solution

by:
m4trix earned 500 total points
ID: 36501772
you have a ton of extra cells to the right and below your main data table. They look blank, but they're still being saved.  Clear them completely (Select Cols T -> XFD, click Clear->All, do the same for all the rows below your data), save the document, and your problem seems to go away

See attached, and note the length of both the vertical and horizontal scroll bars, compared to your version:
 Invoice-log-fixed.xlsm
0
 
LVL 4

Expert Comment

by:javaftper
ID: 36501792
agreed.
m4trix is right.
You may also wish to consider adding error trapping to your code as below. In this example the error description relates to available resources being exceeded. Resources are not necessarily hardware related- in this example it relates to the resources available within the objects being updated.

On Error GoTo Errcatch


    ActiveCell.Offset(1, -8).Range("A1").Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(-1, 0).Range("A1:C1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-R[-1]C[2]"
    ActiveCell.Offset(-1, 10).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Part paid - see balance below"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Balance outstanding"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.Offset(-2, -3).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
    'and at the end of the code put

Exit Sub

Errcatch:
MsgBox Err.Description
End Sub
0
 

Author Closing Comment

by:UplandSystems
ID: 36573895
Fixed the problem, the only issue being some formatting problems
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question