Hi. I would like very much to figure out how to refer to a column using a
variable. The A1 notation does not seem to be conducive to this, and I can't
figure out how to work the syntax.

Basically, I'm creating a macro that will create a data sheet for me. I
enter the number of subjects I have and it creates that many rows. Then I
enter how many trials and it creates that many columns. So for each row, I
want to insert a formula that averages the values for each subject.

For instance, if my data starts in column E and I have 5 trials, I want
column J to be the average of E - I. But depending on how many trials I
have, this formula will differ. Sometimes it will be 6 trials, so column K
will be the average of E - J, and so on.

I have loops that iterate through the columns and rows to set up my
headings, etc. But I can't figure out how to insert the formula that will
calculate a range using a variable name (which is used to iterate through the
loops, hold the current row number, etc). Could someone please provide some
sample code that shows me how to calculate an average using a range selected
by variable names and not A1 notation? I would very much appreciate that.
Thanks in advance.

On Wed, 7 May 2008 10:47:46 -0700, straightedge32
wrote:

Hi. I would like very much to figure out how to refer to a column using a
variable. The A1 notation does not seem to be conducive to this, and I can't
figure out how to work the syntax.

Basically, I'm creating a macro that will create a data sheet for me. I
enter the number of subjects I have and it creates that many rows. Then I
enter how many trials and it creates that many columns. So for each row, I
want to insert a formula that averages the values for each subject.

For instance, if my data starts in column E and I have 5 trials, I want
column J to be the average of E - I. But depending on how many trials I
have, this formula will differ. Sometimes it will be 6 trials, so column K
will be the average of E - J, and so on.

I have loops that iterate through the columns and rows to set up my
headings, etc. But I can't figure out how to insert the formula that will
calculate a range using a variable name (which is used to iterate through the
loops, hold the current row number, etc). Could someone please provide some
sample code that shows me how to calculate an average using a range selected
by variable names and not A1 notation? I would very much appreciate that.
Thanks in advance.

If you have a cell, or a larger range, that is named like
name_of_a_range
then you can use code like this

myVariable = "name_of_a_range"
MsgBox Range(myVariable).Column

to display the column of that cell.

Hope this helps. / Lars-Åke

StartCol = Range("E5").column
for ColCount = StartCol to StartCol + 4
Columns(ColCount).Select
cells(RowNum,Colcount) = 5
next Colcount

"straightedge32" wrote:

Hi. I would like very much to figure out how to refer to a column using a
variable. The A1 notation does not seem to be conducive to this, and I can't
figure out how to work the syntax.

Basically, I'm creating a macro that will create a data sheet for me. I
enter the number of subjects I have and it creates that many rows. Then I
enter how many trials and it creates that many columns. So for each row, I
want to insert a formula that averages the values for each subject.

For instance, if my data starts in column E and I have 5 trials, I want
column J to be the average of E - I. But depending on how many trials I
have, this formula will differ. Sometimes it will be 6 trials, so column K
will be the average of E - J, and so on.

I have loops that iterate through the columns and rows to set up my
headings, etc. But I can't figure out how to insert the formula that will
calculate a range using a variable name (which is used to iterate through the
loops, hold the current row number, etc). Could someone please provide some
sample code that shows me how to calculate an average using a range selected
by variable names and not A1 notation? I would very much appreciate that.
Thanks in advance.

Joel,

Thanks for the swift reply, but I'm afraid I'm a bit lost. Could you
provide some explanation as to what the different parts of the syntax are
doing?

What is the purpose of the first line of code? I'm sorry, I'm just not sure
what's going on here. I don't have enough experience to interpret this.
Thanks again.

-Mike


"Joel" wrote:

StartCol = Range("E5").column
for ColCount = StartCol to StartCol + 4
Columns(ColCount).Select
cells(RowNum,Colcount) = 5
next Colcount

"straightedge32" wrote:

Hi. I would like very much to figure out how to refer to a column using a
variable. The A1 notation does not seem to be conducive to this, and I can't
figure out how to work the syntax.

Basically, I'm creating a macro that will create a data sheet for me. I
enter the number of subjects I have and it creates that many rows. Then I
enter how many trials and it creates that many columns. So for each row, I
want to insert a formula that averages the values for each subject.

For instance, if my data starts in column E and I have 5 trials, I want
column J to be the average of E - I. But depending on how many trials I
have, this formula will differ. Sometimes it will be 6 trials, so column K
will be the average of E - J, and so on.

I have loops that iterate through the columns and rows to set up my
headings, etc. But I can't figure out how to insert the formula that will
calculate a range using a variable name (which is used to iterate through the
loops, hold the current row number, etc). Could someone please provide some
sample code that shows me how to calculate an average using a range selected
by variable names and not A1 notation? I would very much appreciate that.
Thanks in advance.

I have trouble count column numbers. Can you tell me what column number AV
is? To avoid problems let excel caculate the column number. The first line
just tell you that Column E is 5.

The columns method excepts eithyer a number of letters. You can either have
columns("B:C") or columns ("2:3"), they are equivalent.

When you combine a row and a number use the following

Range("A1")

or
x = 7
Range("A" & x).select

or

x = 7
Range("A1:A" & x).select


You can do the same thing with columns
X= 7
Columns("2:" & x).select

"straightedge32" wrote:

Joel,

Thanks for the swift reply, but I'm afraid I'm a bit lost. Could you
provide some explanation as to what the different parts of the syntax are
doing?

What is the purpose of the first line of code? I'm sorry, I'm just not sure
what's going on here. I don't have enough experience to interpret this.
Thanks again.

-Mike


"Joel" wrote:

StartCol = Range("E5").column
for ColCount = StartCol to StartCol + 4
Columns(ColCount).Select
cells(RowNum,Colcount) = 5
next Colcount

"straightedge32" wrote:

Hi. I would like very much to figure out how to refer to a column using a
variable. The A1 notation does not seem to be conducive to this, and I can't
figure out how to work the syntax.

Basically, I'm creating a macro that will create a data sheet for me. I
enter the number of subjects I have and it creates that many rows. Then I
enter how many trials and it creates that many columns. So for each row, I
want to insert a formula that averages the values for each subject.

For instance, if my data starts in column E and I have 5 trials, I want
column J to be the average of E - I. But depending on how many trials I
have, this formula will differ. Sometimes it will be 6 trials, so column K
will be the average of E - J, and so on.

I have loops that iterate through the columns and rows to set up my
headings, etc. But I can't figure out how to insert the formula that will
calculate a range using a variable name (which is used to iterate through the
loops, hold the current row number, etc). Could someone please provide some
sample code that shows me how to calculate an average using a range selected
by variable names and not A1 notation? I would very much appreciate that.
Thanks in advance.

On 7 May, 18:47, straightedge32
wrote:
Hi. *I would like very much to figure out how to refer to a column using a
variable.

I tend to use Cell(r,c) for this sort of thing.

Sheet1.cells(row,col) is a single cell
Sheet1.cells(row,col).entirecolumn will give you a whole column.
Sheet1.cells(row,col).resize(15,1) will give you the top 15 cells.

Alternatively, you can define a range in terms of the corners, so

Sheet1.Range(Sheet1.Cells(toprow, topcol) , Sheet1.Cells(bottomrow,
bottomcells).Values...

Where row, col, toprow etc are variables.

Great, that gets me much closer. So I can use the Cell(r, c) notation to
successfully set a range. So...

Set myRange = wrksht.Range(wrksht.Cells(nCurrRow, 8), wrksht.Cells(nCurrRow,
16)).... etc.

But how do I use this range to calculate a value or put it in a formula?

So I'd like cell (nCurrRow, 17) for instance to be the average of myRange.
Where would I go next? I'd like to be able to put it into a value as well as
to use it as a formula (for autoupdating, etc).

Thank you all very much for your help. Sorry to be so lame at this.

-Mike


"atpgroups" wrote:

On 7 May, 18:47, straightedge32
wrote:
Hi. I would like very much to figure out how to refer to a column using a
variable.

I tend to use Cell(r,c) for this sort of thing.

Sheet1.cells(row,col) is a single cell
Sheet1.cells(row,col).entirecolumn will give you a whole column.
Sheet1.cells(row,col).resize(15,1) will give you the top 15 cells.

Alternatively, you can define a range in terms of the corners, so

Sheet1.Range(Sheet1.Cells(toprow, topcol) , Sheet1.Cells(bottomrow,
bottomcells).Values...

Where row, col, toprow etc are variables.

On 7 May, 22:22, straightedge32
wrote:

Set myRange = wrksht.Range(wrksht.Cells(nCurrRow, 8), wrksht.Cells(nCurrRow,
16)).... etc.

But how do I use this range to calculate a value or put it in a formula?

So I'd like cell (nCurrRow, 17) for instance to be the average of myRange. *

wrksht.cells(nCurrRow,17).Value =
Application.WorkSheetFunctions.Average(myRange).Va lue

Should do the trick.

Don't forget the .Value bit. It is not entirely necessary for .Value
as that is the default property, but it makes it clear you are not
trying to redefine a range but instead work on the data in that
range.

I tried doing just what you said, copying the syntax appropriately, but I
just get an error message stating "Run time error 438: Object Doesn't Support
this property or method".

"wrksht" is set to the active worksheet. myRange was declared of type
Range. I don't understand why this wouldn't work. It appears that Average
won't work as a function here. Is that correct?

-M

"atpgroups" wrote:

On 7 May, 22:22, straightedge32
wrote:

Set myRange = wrksht.Range(wrksht.Cells(nCurrRow, 8), wrksht.Cells(nCurrRow,
16)).... etc.

But how do I use this range to calculate a value or put it in a formula?

So I'd like cell (nCurrRow, 17) for instance to be the average of myRange.

wrksht.cells(nCurrRow,17).Value =
Application.WorkSheetFunctions.Average(myRange).Va lue

Should do the trick.

Don't forget the .Value bit. It is not entirely necessary for .Value
as that is the default property, but it makes it clear you are not
trying to redefine a range but instead work on the data in that
range.

On 8 May, 01:13, straightedge32
wrote:

I tried doing just what you said, copying the syntax appropriately, but I
just get an error message stating "Run time error 438: Object Doesn't Support
this property or method".

Sorry, my mistake, I typed that reply last night on a machine without
Excel installed and I got it wrong (twice)

Set myRange = wrksht.Range(wrksht.Cells(nCurrRow, 8),
wrksht.Cells(nCurrRow, 16))
wrksht.Cells(nCurrRow, 17).Value =
Application.WorksheetFunction.Average(myRange.Valu e)

Will work. "Object Doesn't Support this property or Method" was due to
me inventing a new method "WorkSheetFunctions" when it is
"WorkSheetFunction"
Also, the .Value applies to myRange, not to Average() so it was in the
wrong place.
Sorry for any confusion I might have caused.