Question About ActiveCell.Cells(x, y).Select

[ParrotGirl]

I am editing a macro written by somebody else a number of years ago. (You all know the type. Gives his variables cryptic names, such as "SomeArray4". Throws in 1001 "GoTo" statements, just for good measure. Doesn't document anything with comments.)

I am a programmer from way back in the 1980s, and am very familiar with Visual Basic. However, I have not used Excel that much, and am new to Visual Basic for Applications. I volunteered to help my husband with editing some macros at the company he works for.

The programmer who wrote these macros is using a very unusual method for reading and writing information to the worksheets. Rather than using Range("[range info]").Select, he is using ActiveCell.Cells(x,y).Select. Below is a block of the code, and then I will tell you what is occurring. (Note: I have changed the variable names because I thought they might identify the company.)

    Counter = 1
 
    Sheets("Sheet1").Select
    Rows("2:65536").Select
    [Sort method here...]
    Range("c2").Select
 
    Do Until ActiveCell.Offset(0, 0) = ""
        ArrayVar1(Counter) = ActiveCell.Offset(0, -2)
        ArrayVar2(Counter) = ActiveCell.Offset(0, 0)
        ArrayVar3(Counter) = ActiveCell.Offset(0, 2)
        ArrayVar4(Counter) = ActiveCell.Offset(0, 4)
        ArrayVar5(Counter) = ArrayVar5(Counter) + ActiveCell.Offset(0, 5)
        ActiveCell.Cells(2, 1).Select
 
        Do Until ActiveCell.Offset(0, 0) = ""
            If ActiveCell.Offset(0, 0) <> ArrayVar2(Counter) Then GoTo 20
            ArrayVar5(Counter) = ArrayVar5(Counter) + ActiveCell.Offset(0, 5)
            ActiveCell.Cells(2, 1).Select
        Loop
 
20
    Counter = Counter + 1
    Loop

Okay, so that's the code. It's a double-nested "Do Until". I wondered what the "ActiveCell.Cells(2,1).Select" statement was doing. Was it positioning the cursor two rows down and 1 column over? I couldn't figure out how that statement was working until I took it through debug and actually watched it selecting. And the results totally confused me.

It appears to be selecting a cell that is 1 row down and 0 columns over from the currently active cell. In other words, take the (x, y) and subtract 1 from each: ((x-1),(y-1)) and that will tell you what cell will be selected next. In this case: ((2-1=1),(1-1=0)), so the active cell moves 1 row down and 0 columns over.

He uses the same statement for writing out data to a new sheet, when he wants to start a new row: ActiveCell.Cells(2,1).Select. I suppose he uses this because the number of records being processed varies from several hundred to over 5000.

I have not been able to find any information about what this statement is doing, but I verified that, indeed, 1 is always subtracted from the row and column values. I verified it by writing a double nested loop

    Range("a1").Select
    For i = 1 to 5
        For j = 1 to 4
            ActiveCell.Cells(i,j).Select
            [Statements using the Wait Method; Wait 10 seconds]
        Next j
    Next i

Do this, and you can watch the active cell moving in very strange ways! It ends up down on row 61 and out on column AE.

So, does anyone have any documentation on this?

I would like, also, to know how to use the Range("[value]").Select method when you don't know ahead of time how many rows you will have. I tried stringing together a row and column number, and then just putting the variable into the Select method:

    strMyRange = "A"        'This would be obtained from column index
    strMyRange = strMyRange & "1"   'Row number
    strMyRange = Chr$(34) & strMyRange & Chr$(34)  'Put quotes around it
    Range(strMyRange).Select

I got an error message from this. I think maybe I need a variable whose type is Range, instead of a string? "Dim rngMyRange As Range" maybe? As usual, the help file is less than helpful. It doesn't even list Range as a data type. But I have brought up examples through the online help that dimension variables as ranges! (I swear one of these days I'm going to shoot that f*@! paperclip!)

Thanks in advance for your help!

 

[VoG]

Hello and welcome to MrExcel.

For the easy part of your question

strmyrange = "A"        'This would be obtained from column index
strmyrange = strmyrange & "1"   'Row number
Range(strmyrange).Select

although you don't actually need to Select that range to work with it, for example

strmyrange = "A"        'This would be obtained from column index
strmyrange = strmyrange & "1"   'Row number
Range(strmyrange).Copy Destination:=Range("G11")

Also for manipulating both rows and columns you might find it easier to use

myrow = 2
mycol = 4
Cells(myrow, mycol).Select