Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
Hi all,
I'm currently using MS Excel 2004 for Mac and i'm just having some trouble with ranges in the VBA section. How do I make it such that Ranges that I want to select are varied? Like, I know we can use something like: "Sheet1.Range("A1:A500").Select" to select that particular range. However, how do I make it such that the range selected will change based on another variable? Like, I want the contents of the Range option to change if another variables. Something similar to the functionality of the Cells option, where you can use something like Sheet1.Cells(i+k, 7).Select to change the cell selected based on other variable, in this case the two integer variables i and k. Basically, this is what i'm trying to achieve: I have many variables for statistical analysis, which i'm sorting by columns on a seperate sheet. I've created a table that summarises the variables that I have, how many entries and what-not. From there, I have checkboxes next to the table to indicate what kind of variable they a "Independent", "Dependent" and "not used". (It's for multiple regression analysis) From those options, the vba will then find the variables that are either independent or dependent, sort them out on another sheet, then call up the Regression tool from the Analysis Toolpack, run the regression, strip the results for me on another sheet and then clear again. The reason I wanted to do that is because the usual Excel Regression is really annoying in that I have to rearrange the columns everytime I want to do a new regression. I normally use professional statistical software to do this kind of thing, but I don't have access to them at this new workplace. Please help. Thank you very much in advance! Kind Regards, Iwan Juwono |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
You're not very specific about exactly how you want to use variables
with Range(), but perhaps you mean something like Sheet1.Range("A" & i & ":A" & k).Select You can also use Cells and resize: Sheet1.Cells(i + k, 7).Resize(m, n).Selecdt In article , Harimau wrote: Hi all, I'm currently using MS Excel 2004 for Mac and i'm just having some trouble with ranges in the VBA section. How do I make it such that Ranges that I want to select are varied? Like, I know we can use something like: "Sheet1.Range("A1:A500").Select" to select that particular range. However, how do I make it such that the range selected will change based on another variable? Like, I want the contents of the Range option to change if another variables. Something similar to the functionality of the Cells option, where you can use something like Sheet1.Cells(i+k, 7).Select to change the cell selected based on other variable, in this case the two integer variables i and k. Basically, this is what i'm trying to achieve: I have many variables for statistical analysis, which i'm sorting by columns on a seperate sheet. I've created a table that summarises the variables that I have, how many entries and what-not. From there, I have checkboxes next to the table to indicate what kind of variable they a "Independent", "Dependent" and "not used". (It's for multiple regression analysis) From those options, the vba will then find the variables that are either independent or dependent, sort them out on another sheet, then call up the Regression tool from the Analysis Toolpack, run the regression, strip the results for me on another sheet and then clear again. The reason I wanted to do that is because the usual Excel Regression is really annoying in that I have to rearrange the columns everytime I want to do a new regression. I normally use professional statistical software to do this kind of thing, but I don't have access to them at this new workplace. Please help. Thank you very much in advance! Kind Regards, Iwan Juwono |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
Hi Iwan -
I'm sure you're aware that there are many (many!) ways to use variables for selecting ranges. Start by looking up the Range Property in the online help system. A variation of the Range property may work for you. An example is: Sub Iwan() Dim ws1 As Worksheet irow = 2: icol = 5 ' starting row and column Set ws1 = Worksheets(1) Range(ws1.Cells(irow, icol), ws1.Cells(ws1.UsedRange.Rows.Count, icol)).Select End Sub -- Jay "Harimau" wrote: Hi all, I'm currently using MS Excel 2004 for Mac and i'm just having some trouble with ranges in the VBA section. How do I make it such that Ranges that I want to select are varied? Like, I know we can use something like: "Sheet1.Range("A1:A500").Select" to select that particular range. However, how do I make it such that the range selected will change based on another variable? Like, I want the contents of the Range option to change if another variables. Something similar to the functionality of the Cells option, where you can use something like Sheet1.Cells(i+k, 7).Select to change the cell selected based on other variable, in this case the two integer variables i and k. Basically, this is what i'm trying to achieve: I have many variables for statistical analysis, which i'm sorting by columns on a seperate sheet. I've created a table that summarises the variables that I have, how many entries and what-not. From there, I have checkboxes next to the table to indicate what kind of variable they a "Independent", "Dependent" and "not used". (It's for multiple regression analysis) From those options, the vba will then find the variables that are either independent or dependent, sort them out on another sheet, then call up the Regression tool from the Analysis Toolpack, run the regression, strip the results for me on another sheet and then clear again. The reason I wanted to do that is because the usual Excel Regression is really annoying in that I have to rearrange the columns everytime I want to do a new regression. I normally use professional statistical software to do this kind of thing, but I don't have access to them at this new workplace. Please help. Thank you very much in advance! Kind Regards, Iwan Juwono |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
Yes, that's what I wanted to know.
Thank you very much to the both of you, I hope this thing works. _< I managed to make excel crash five times already by writing VBA in it. Strange. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
Thank you again, this proved to be a lifesaver.
"Jay" wrote: Hi Iwan - I'm sure you're aware that there are many (many!) ways to use variables for selecting ranges. Start by looking up the Range Property in the online help system. A variation of the Range property may work for you. An example is: Sub Iwan() Dim ws1 As Worksheet irow = 2: icol = 5 ' starting row and column Set ws1 = Worksheets(1) Range(ws1.Cells(irow, icol), ws1.Cells(ws1.UsedRange.Rows.Count, icol)).Select End Sub -- Jay "Harimau" wrote: Hi all, I'm currently using MS Excel 2004 for Mac and i'm just having some trouble with ranges in the VBA section. How do I make it such that Ranges that I want to select are varied? Like, I know we can use something like: "Sheet1.Range("A1:A500").Select" to select that particular range. However, how do I make it such that the range selected will change based on another variable? Like, I want the contents of the Range option to change if another variables. Something similar to the functionality of the Cells option, where you can use something like Sheet1.Cells(i+k, 7).Select to change the cell selected based on other variable, in this case the two integer variables i and k. Basically, this is what i'm trying to achieve: I have many variables for statistical analysis, which i'm sorting by columns on a seperate sheet. I've created a table that summarises the variables that I have, how many entries and what-not. From there, I have checkboxes next to the table to indicate what kind of variable they a "Independent", "Dependent" and "not used". (It's for multiple regression analysis) From those options, the vba will then find the variables that are either independent or dependent, sort them out on another sheet, then call up the Regression tool from the Analysis Toolpack, run the regression, strip the results for me on another sheet and then clear again. The reason I wanted to do that is because the usual Excel Regression is really annoying in that I have to rearrange the columns everytime I want to do a new regression. I normally use professional statistical software to do this kind of thing, but I don't have access to them at this new workplace. Please help. Thank you very much in advance! Kind Regards, Iwan Juwono |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA help - Range selection
"Harimau" wrote:
... I normally use professional statistical software to do this kind of thing, but I don't have access to them at this new workplace. R is freely downloadable from www.r-project.org Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you do a range selection dialog like those many in Excel? | Excel Discussion (Misc queries) | |||
Excel 2007 Range Selection | Excel Discussion (Misc queries) | |||
range selection for a sort on a excel db | Excel Programming | |||
VBA : Excel Range Selection Problem | Excel Programming | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming |