Solved

VBA Code

Posted on 2000-04-20
10
281 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

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 …
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

770 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