Tech Support Guy banner
Status
Not open for further replies.
1 - 5 of 5 Posts

·
Registered
Joined
·
2 Posts
Discussion Starter · #1 ·
Scenario:
Excel spreadsheet 1 with 'X' number of lines in it.

Goal:
Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

Example:
First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x
x = "=COUNTA(CSEXPORT!C:C)-1"

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.

Thanks
 

Attachments

·
Registered
Joined
·
19,896 Posts
Try this t find x -

Dim x As Integer
Range("a1").Select
Selection.CurrentRegion.Select
x = Selection.Rows.Count + 1
MsgBox x
The plus 1 gives you 10, the actual number of rows you have is 9.
To paste x rows use

dim y
for y =1 to x
activesheet.paste
activecell.offset(1,0)
next y
 

·
Registered
Joined
·
2 Posts
Discussion Starter · #3 ·
Setting of the variable worked as you said.

The For loop did not work exactly right, but it did get me headed down the path I needed to get a solution that works.

Thanks for the help.
 

·
Registered
Joined
·
4,916 Posts
The standard way of selecting a cell using a variable row is
Code:
Range("C" & x).Select
where "x" equals the row variable and "C" equals the column.

You can also use
Code:
Cells(x,y).Select
where "x" equals the row index number and "y" equals the column index number. So, if you use the statement Cells(2,2).select you will be taken to column B row 2.

Rollin
 
1 - 5 of 5 Posts
Status
Not open for further replies.
Top