none
Need help understanding Cells, Offset, and Address functions within a Split function RRS feed

  • Question

  • Hello,

    I am trying to troubleshoot a macro that someone else wrote and am trying to understand what the following code does:

    Split(Cells(1, A).Offset(0, 1).Address, "$")(1)

    The Offset function appears to be missing a reference, height, and width, and I'm confused as to how these functions are interacting with each other within the Split function. Any help would be greatly appreciated.

    Thanks

    Wednesday, August 9, 2017 2:40 PM

All replies

  • A must be a variable whose value holds a column number. Let's say that A = 6.

    Cells(1, A) is the cell on the active sheet in row 1 and column number A. In our example: cell F6.

    Cells(1, A).Offset(0, 1) is the cell 0 rows down and 1 column to the right of that cell, i.e. the cell in row 1 and column (A+1). In our example: cell G6.

    Cells(1, A).Offset(0, 1).Address is the address of this cell. By default, the address is given as an absolute reference: $G$6.

    Split(Cells(1, A).Offset(0, 1).Address, "$") returns an array whose elements are the parts of "$G$6" when you split it on "$". This is an array of 3 elements: {"","G","6"}: the empty string "" before the first "$", the "G" between the two "$"s and the "6" after the second "$".

    The elements of this array are numbered starting at 0, so the elements are numbered 0, 1 and 2.

    Split(Cells(1, A).Offset(0, 1).Address, "$")(1) returns the middle element of the array: "G". This is the column letter.

    So what the code does is return the column letter (or letters) of the column to the right of column number A.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 9, 2017 2:58 PM