Solved

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

Posted on 2011-09-08
3
237 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now