View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
straightedge32 straightedge32 is offline
external usenet poster
 
Posts: 5
Default Refer to Column with a variable

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.