?
Solved

MS Excel VB copying a formula in to an undertermined amount of rows

Posted on 1998-08-19
3
Medium Priority
?
146 Views
Last Modified: 2010-04-30
Having recorded an extensive VB macro which sorts, adds/deletes, modifies, and recalculates a fairly large ASCII file, I run in to a problem when additional records are added to this file.  Initially, part of the macro was set to copy a formula in cell D1 and paste it to an inserted column C1:C1129.  Now that additional records have been added, the macro still only pastes to the extent of C1129.  How can I have the macro copy the information in cell D1 and paste it to an (un)-predetermined amount of rows in column C to compensate for any additional records added?  
0
Comment
Question by:astrnzo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Accepted Solution

by:
brewer earned 300 total points
ID: 1429922
Hi!

The problem maybe is  that you have defined a fixed number of rows or column in your code.

Try to insert an algorithm that will first determine the last row or column of the data and let it be the maximum number of row or column especially in your loops.

I hope this will help you. Just tell if you need a sample of this code, I'll be very glad to send it to you.

alderiteij@hotmail.com
0
 
LVL 3

Expert Comment

by:frazer
ID: 1429923
Hi,

The code you want is....

Sub Copying()
    Range("D1", Range("C65536").End(xlUp).Offset(0, 1)).Select
End Sub

This will select D1:D??? based on column C being filled....i.e. if column C is filled to row 13 then Range D1:D13 will be selected.

Hope this helps

Frazer
0
 
LVL 3

Expert Comment

by:frazer
ID: 1429924
This also assumes you are using excel 97, if you are not you will need to change C65536 to C16384...

Frazer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

771 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