• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

VBA Code

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
perezm
Asked:
perezm
  • 7
  • 3
1 Solution
 
calacucciaCommented:
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
 
calacucciaCommented:
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
 
calacucciaCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
calacucciaCommented:
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
 
perezmAuthor Commented:
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
 
calacucciaCommented:
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
 
perezmAuthor Commented:
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
 
calacucciaCommented:
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
 
perezmAuthor Commented:
You were right.  I had an extra space.  Sorry, I missed that.

Again, thanks for relieving my headache.

Have a great day.

Marco.
0
 
calacucciaCommented:
You're Wellcome, Marco.

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

Cheers
Calacuccia
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now