Solved

VBA Code

Posted on 2000-04-20
10
279 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
ID: 2734994
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
ID: 2735096
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
ID: 2735099
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
ID: 2735110
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
ID: 2735142
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
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.

 
LVL 17

Expert Comment

by:calacuccia
ID: 2735179
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
ID: 2735303
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
ID: 2735320
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
ID: 2735364
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
ID: 2735607
You're Wellcome, Marco.

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

Cheers
Calacuccia
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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

20 Experts available now in Live!

Get 1:1 Help Now