Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to Column with a variable
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refer to Public Variable from Another Workbook | Excel Programming | |||
How to refer variable while assigning string value? | Excel Programming | |||
variable to refer to "this worksheet"? | Excel Worksheet Functions | |||
Using a variable to refer a cell | Excel Programming | |||
How to refer a Variable across Workbooks ? | Excel Programming |