Solved

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

Posted on 1998-08-19
3
140 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 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

17 Experts available now in Live!

Get 1:1 Help Now