ActiveCell.Offset Question

  • Good morning all,


    I have a loop that I am having trouble getting to work the way I desire. The purpose of this loop is to look in column "J", and if the value ISTEXT, offset the value 1 column,
    However what is happening is that it sees the first cell in the column, and it is in fact text, and it is just offsetting 1 column, not moving the value, or continuing the loop,


    Here is what I have so far, any help or guidance is appreciated,


  • I don't understand what you're actually trying to do. looking at your code. it technically does nothing. Tthe "Activecell.Offset(0,1).Value" segment doesn't actually populate anything and it cant be used to move anything around.
    1) Are you trying to check if current cell is Text?
    2) Basis that I assume you actually want to move the value from the current cell into the column on the right of that cell?
    3) Is it just value in current cell you want to move to right or you want the row to move. i.e. if current column is J and the value is text then move the current cell as well as any cells on the right, 1 cell to the right?

  • Correct, I am trying to check if the active cell ISTEXT, and if it is, move that cell over one column to K, and loop until I come to the first blank cell

  • Hello,


    You could test the following



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Modified your code to do what you want. I wasn't sure if you also wanted to clear the activecell after the data has been moved over so I've added that bit of code in. Remove the "ActiveCell.ClearContents" argument if u don't want that to happen.


  • Carim & chirayuw ,
    Thank you both for your time and help, Both of these solutions do what I need.


    Glad you could fix your problem ...:wink:


    When using a macro ... you should avoid all the Select and Activate ... which are not necessary ... and which slow down the macro execution ...


    Thanks ... for your Thanks ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!