[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 1998-08-19
3
Medium Priority
?
149 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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

872 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