Solved

VBA Code

Posted on 2000-04-20
10
278 Views
Last Modified: 2012-08-14
I am working with VBA in Excel.  I have added a command button. I would like to copy a row/column A8 through F8 (formulas) and then fill (paste?) these formulas down based on a number entered in Cell B1.  I have found many techniques for Macro Sheets (that do not work in VBA), but nothing that applied to VBA.

I would appreciate any assistance and/or explanation.

Thanks
0
Comment
Question by:perezm
  • 7
  • 3
10 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 100 total points
Comment Utility
Hi Perezm,

This little sub shows how to do this:

Sub GetStarted()
'Declare HowMany variable as a long
Dim HowMany As Long
'Get contents of cell B1 and store value in variable HowMany
HowMany = Sheets("Sheet1").Range("B1")
'Copy your range A8:F8
'and paste it down to row 9 to row HowMany + 8
Sheets("Sheet1").Range("A8:F8").Copy Sheets("Sheet1").Range("A9:A" & HowMany + 8)
End Sub

That should do what you need.

To assign it to the command button, right-click on command button and select assign Macro, then choose this macro.

If using a command button from the Control Toolbox, right-click on it, Select view Code, and put this line in the Sub CommandButton_Click
Sub CommandButton1_Click()
GetStarted
End Sub

Hope this helps & learns you somethying
Calacuccia
Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
More explanation:

You can select cells explicitely and make Excel go to these cells by calling a range with the select statement.

Range must be preceded by sheet to prevent the wrong sheet to be handled.

There are several methods of selecting a range:

Sheets("Sheet1").Range("A1").Select
This will select cell A1 of sheet 1 in the active workbook.

Sheets("Sheet1").Cells(1,1).Select
This will also give cell A1 of sheet 1 of active workbook

If referring to a sheet in another Workbook (example for FileName.xls), use:
Workbooks("FileName.xls").Sheets("Sheet1").Cells(1,1).Select

You don't have to select the range though to do operations on it.
For example, the value of cell C3 in sheet1 of the ActiveWorkbook can be assigned to a variable a, by the command
a = ActiveWorkbook.Sheets("Sheet1").Cells(3,3)
or
a = ActiveWorkbook.Sheets("Sheet1").Range("C3")

You can also refer to named ranges. To name a range, select one while in Excel (can be multiple cells), then do Insert/Name/Define and type in the top box the name you want to give to this range (e.g; MyRange)

When referring to it in VBA, you can do this as follows
Range("MyRange").Select

The copy & paste  command can be put in one line as in the example macro you asked for:
Syntax
SourceRange.Copy DestinationRange

In the macro above SourceRange = Sheets("Sheet1").Range("A8:F8")
and DestinationRange = Sheets("Sheet1").Range("A9:A" & HowMany + 8)

The & operator is used to concatenate different sections of text to one text. The Range argument in this last example requires something like:
Range("A9:A15")

This is done by concatenating "A9:A" and the number returned by HowMany + 8, so if HowMany = 7 then you'll get the needed result.

Another way to refer to a range consisted of a block of cells is using the Cells property inside the range property
Range(Cells(1,1),Cells(8,1) is the same as Range("A1:A8")

Have Fun
Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
More explanation:

You can select cells explicitely and make Excel go to these cells by calling a range with the select statement.

Range must be preceded by sheet to prevent the wrong sheet to be handled.

There are several methods of selecting a range:

Sheets("Sheet1").Range("A1").Select
This will select cell A1 of sheet 1 in the active workbook.

Sheets("Sheet1").Cells(1,1).Select
This will also give cell A1 of sheet 1 of active workbook

If referring to a sheet in another Workbook (example for FileName.xls), use:
Workbooks("FileName.xls").Sheets("Sheet1").Cells(1,1).Select

You don't have to select the range though to do operations on it.
For example, the value of cell C3 in sheet1 of the ActiveWorkbook can be assigned to a variable a, by the command
a = ActiveWorkbook.Sheets("Sheet1").Cells(3,3)
or
a = ActiveWorkbook.Sheets("Sheet1").Range("C3")

You can also refer to named ranges. To name a range, select one while in Excel (can be multiple cells), then do Insert/Name/Define and type in the top box the name you want to give to this range (e.g; MyRange)

When referring to it in VBA, you can do this as follows
Range("MyRange").Select

The copy & paste  command can be put in one line as in the example macro you asked for:
Syntax
SourceRange.Copy DestinationRange

In the macro above SourceRange = Sheets("Sheet1").Range("A8:F8")
and DestinationRange = Sheets("Sheet1").Range("A9:A" & HowMany + 8)

The & operator is used to concatenate different sections of text to one text. The Range argument in this last example requires something like:
Range("A9:A15")

This is done by concatenating "A9:A" and the number returned by HowMany + 8, so if HowMany = 7 then you'll get the needed result.

Another way to refer to a range consisted of a block of cells is using the Cells property inside the range property
Range(Cells(1,1),Cells(8,1)) is the same as Range("A1:A8")

Have Fun
Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Sorry for posting twice, there was an error at the last line
Range(Cells(1,1),Cells(8,1) is the same as Range("A1:A8")
That should be (as in last post)
Range(Cells(1,1),Cells(8,1)) is the same as Range("A1:A8")

Calacuccia
0
 
LVL 1

Author Comment

by:perezm
Comment Utility
The information that you provided is very good.  And from what I can see makes a lot of sense, but I keep getting an error on the following line:

Sheets("Sheet1").Range("A9:A" & HowMany + 8)

I am working with Excel 97 and it doesn't seem to like this line.

I have tried some modifications, but none of them have worked.

Any ideas would be appreciated.

Thanks.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Some questions:

(the could should work and is written in Excel 97)


- Do you actually have a Sheet1.

I forgot to tell you, but Sheets("Sheet1") refers to a worksheet with the name sheet1 (on sheet tab).

You should change the name used in the macro to your name, or if you call the macro from the sheet, use:
Activesheet..Range("A9:A" & HowMany + 8)

In fact as you use a command button, I would replace every Sheets("sheet1") by ActiveSheet.

- Another possibility.. are you positive that this command is on the same line (there may be no carriage return between both)
Sheets("Sheet1").Range("A8:F8").Copy Sheets("Sheet1").Range("A9:A" & HowMany + 8)

- Third thing to try:
Sheets("Sheet1").Range("A8:F8").Copy Sheets("Sheet1").Range(Cells(9,1),Cells(HowMany+8,1))
or
Sheets("Sheet1").Range("A8:F8").Copy Sheets("Sheet1").Range(Cells(9,1),Cells(HowMany+8,6))

- Make sure range B1 contains a value higher than 0.

Good Luck
Calacuccia

0
 
LVL 1

Author Comment

by:perezm
Comment Utility
Excellent.  This is exactly what I have been trying to accomplish.

I tried them one at a time.  The one that worked:

Sheets("Sheet1").Range(Cells(9,1),Cells(HowMany+8,1)) .

But, I don't understand why this is the one that worked.  The other alternatives seemed to make sense as to why they should work.
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
So this one did not worked...
Sheets("Sheet1").Range("A8:F8").Copy Sheets("Sheet1").Range("A9:A" & HowMany + 8)

Is there a space between the last "A9:A" and &
                        between & and HowMany
                        between HowMany and +
                        between + and 8

That could cause errors..

If not, please provide more info about the error message you got, that will help.

Calacuccia
0
 
LVL 1

Author Comment

by:perezm
Comment Utility
You were right.  I had an extra space.  Sorry, I missed that.

Again, thanks for relieving my headache.

Have a great day.

Marco.
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
You're Wellcome, Marco.

You too, try to enjoy this day and the weekend.

Cheers
Calacuccia
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now