Defining a Range in Excel

[rambob]

I am running Excel 97 and am getting an error when I try to define a range using the following statement:

Set range1 = Worksheets("Testing divisions&quot.Cells.Range(2, 1, 10, 5)

I saw this method referenced at another help site. I need to define a range using row and column defined as integers instead of the A1 format. Can anyone help?

 

[Davefeet]

what you need to do is rename the A1 or any row or column to what you want. To do this you need to select the column, to the left of the columns is a window that tells you what cloumn is selected, change that to what you need. The only problem with that is that you can't rename it a row/column already used. And then maybe in your definition instead of using integers use the name of the column.
I dont think that is what you wanted but it might help.

Good luck Salt Lake City Home of 2002 Winter Olympics

 

[Davefeet]

Also Check out the INSERT menu down to "name".
Good luck Salt Lake City Home of 2002 Winter Olympics

 

[rambob]

This is in a macro so can't use menuing or manual commands.

 

[SkipVought]

Hi rambob,
You came to the right place. I HATE A1 notation and use it rarely.

I prefer Cells(row, col) notation. see the Cells Property help in VBA.

I am not sure what you are driving at in your example above, but a simple range definition using cells would be...

Set range1 = Worksheets(x).Range(Cells(1, 1), Cells(1, 1).End(xlToRight))

IF you had a table on Worksheet(x), then range1 would define the headings for that table if the headings were in row 1.

VOLA Skip,
metzgsk@voughtaircraft.com

 

[rambob]

Skip,

What I found in the other help site was that you could define a range by the (beginning row, beginning column, for howm many rows, for how many columns). So, in my original example, I would define a range from A2 to E5 (starting at 2nd row, first column for 10 rows and 5 columns).

I want to iterate through a range of rows and columns and want to use rowindex and colindex but can't figure out how to define that range using integers.

 

[acron]

Try,

Dim Range1 As Range
Set Range1 = .Sheets("x").Range(Cells(2, 1), Cells(10, 5))

AC

 

[SkipVought]

Here's a way to incriment thru all the cells, and define some useful properties for the range...

Sub Main()
    Dim rng As Range, MyValue
    Dim lRow As Long, iCol As Integer
    Dim lRowStart As Long, iColStart As Integer
    Dim lRowCount As Long, iColCount As Integer
    
    Set rng = Range(Cells(2, "A"), Cells(5, "E"))
    
    With rng
        lRowCount = .Rows.Count
        lRowStart = .Row
        iColCount = .Columns.Count
        iColStart = .Column
    End With

    For lRow = lRowStart To lRowCount + lRowStart - 1
        For iCol = iColStart To iColCount + iColStart - 1
            MyValue = Cells(lRow, iCol).Value
            'other stuff
        Next
    Next
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[rambob]

Thanks very much guys...both of these work well. I really appreciate your time and responses.