Excel VBA help - Range selection

[Guest]

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 are: "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

 

[JE McGimpsey]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Thank you again, this proved to be a lifesaver.

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

 

[Guest]

... 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