?
Solved

VBA Code

Posted on 2000-04-20
10
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 400 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

764 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