With statement returns unexpected result

[tiredofit]

I have values in cells A1, B1 and C1.

I want to shift cell B1 to the right, so the value in C1 is shifted to D1 and the value in B1 is shifted to C1.

Then I want to put the value of "abc" into cell B1.

This is my code:

Range("B1").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
Range("B1").Value2 = "abc"

This works as expected.


However, I thought it would be compact if I used a With Statement, so I changed it to:

With Range("B1")
   
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    .Value2 = "abc"
   
End With

What I found surprising was after running this second piece of code, cell B1 is blank and cell C1 contains the value "abc".

I expected cell B1 to contain the value "abc".

What is wrong?

Thanks

 

[RoryA]

Your With statement is holding a reference to a cell. Your first statement moves that cell, but the reference is still to that same cell, so the next line writes to C1. Your original code refers to B1 each time, but the second time B1 is not the same cell as it was in the first line.

 

[tiredofit]

Your With statement is holding a reference to a cell. Your first statement moves that cell, but the reference is still to that same cell, so the next line writes to C1. Your original code refers to B1 each time, but the second time B1 is not the same cell as it was in the first line.

Thanks for the explanation.

Seems I must be cautious when tidying up code using With. It's not identical to factorising in maths.

 

[saboh12617]

Hello,

That's an interesting question. Here is the explanation. Think of the "With" as an anchor on a cell and Insert as a real insertion of new data. When you do the insertion, you actually move the cell B1 to the right, so in C1. So then, when you assign a value, your "with cell" is no longer in B1 but in C1. Here below you should run this code to understand the cell "moving around".

Sub test()
  With Range("B1")
      Debug.Print .Address  ' will show B1
      .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      Debug.Print .Address  ' will show C1
  End With
End Sub

To compensate you can use a .Offset, or simply do as you did first, so you reevaluate what is at the address "B1".

 

[RoryA]

Using With is like using a variable - your revised code is essentially the same as:

Dim cell As Range
Set cell = Range("B1")
cell.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  
cell.Value2 = "abc"

The Range property actually returns a different pointer each time you call it, even if you are referring to the same cell (which is why it's usually pointless testing range variables using Is)

 

[tiredofit]

Thanks to both for the example.