none
How to offset a user-inputted cell selection in Excel using Macro... RRS feed

  • General discussion

  • Hello,

    I am writing a line of code as part of a more complex program that is not working correctly. I am new to VBA so please bear with me...

    Essentially, I am prompting the user to select a cell which is then assigned to the variable 'celNm'.

    I then perform the following actions:

    celNm.EntireRow.Copy
    celNm
    .EntireRow.Insert

    Next, for reasons specific to the program (and I am assuming the celNm will be located at the same cell after the command [Not the same location]), I want to move the cell selection upwards, so it is now located at the row just recently copied above. I am using the following line to do this:

    celNm.Offset(-1, 0).Select

    This, however, does not work.

    The next step in the program would be to create a list in this location. However the program still creates a list at the previous location (in the cell selected). Why is this?

    Tuesday, July 12, 2011 6:23 PM

All replies

  • That happens because after the .Select command you need to use Selection, not celNm, as your range object. That is because your line does not change the range celNm, but it does change the selection.

    But you could replace
    celNm.Offset(-1, 0).Select
    with
    Set celNm = celNm.Offset(-1, 0)
    and then you could continue to use celNm as your range object.


    HTH, Bernie


    Tuesday, July 12, 2011 8:02 PM