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.


    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 (

    Wednesday, August 9, 2017 2:58 PM