VBA Selection.offset(0,18) as an object, but it moves too far

Question

My vba code requires me to move 18 cells to the right. Since I use "Selection.offset(0,18)" a lot, I decided to turn into into a variable. But every time I use "MoveEighteen.select" it will move about 50 cells to the right. This only happens after the IF statements. The very first MoveEighteen works fine.

   Dim AnchorDate As Range
    Dim CompareDate As Range
    Dim MoveEighteen As Range
    Set AnchorDate = Range("b2")
    Set MoveEighteen = Selection.Offset(0, 18)

    Range("B2").FormulaR1C1 = "=int(NOW())"
    Range("B2").Copy
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B5").Select


    If Selection = AnchorDate Then
    MoveEighteen.Select

    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    ActiveCell.Offset(1, -3).Select`


    Do Until Selection <> AnchorDate
    MoveEighteen.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'go tonext date
    ActiveCell.Offset(1, -3).Select
    Loop
    
    Else: Selection.Select
    
    Else: Selection.Select
        If Selection < AnchorDate Then
            Do Until Selection = AnchorDate
            Selection.Offset(1, 0).Select
            Loop
        End If
        
        If Selection = AnchorDate Then
        MoveEighteen.Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False`

`Set MoveEighteen = Selection.Offset(0, 18)` - this only runs *once*, so you `MoveEighteen` will always be the same range. You best bet to fix your code is to not use Select/Activate. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Dec 07 '21 at 22:58

NickSlash · Answer 1 · 2021-12-07T23:12:50.963

0

Might not understand how you're using it, but turning it into a variable makes it static, you may want a function instead?

Function MoveEighteen(target As Range) as Range
Set MoveEighteen = target.offset(0,18)
End Function

I think the unexplaned movement of the reference is a result of your paste command? I know formulas do that sort of thing when you paste, but not sure if VBA does this too. After testing, not sure this is the case.

edited Dec 07 '21 at 23:12

answered Dec 07 '21 at 22:58

NickSlash

4,758
3
21
38

I realize setting this as an object will only work once. I would have to set Moveeighteen every time I want to use MoveEighteen... which defeats the purpose of using MoveEighteen. Is there any way to make sure set objects dont remove their value after its used? – lmac Dec 08 '21 at 13:30

VBA Selection.offset(0,18) as an object, but it moves too far

1 Answers1