VBA move value to adjecent cell

Question

OK its just simple two lines of Code that for some reason don't work as expected. I'm sure its something simple but I can't put my finger on it:

Dim Series as Range Dim i as Integer Set Series = Range("A1:B1") i = 1 ' Following two lines apparently don't do anything ' I would have expected them to move value of cell B1 to A1, and make B1 blank Series.Value2(1, i) = Series.Value2(1, i + 1) Series.Value2(1, i + 1) = ""

Example:

 A B 1 "" 1

Desired Result:

 A B 1 1 ""

Again I know its a very simple( and simplified) code but I just can't figure it out.

That's not how I would do that task; but I can confirm it does not work with Excel 2007, and I'm not sure why. In safe mode, the code causes Excel to crash (shut down). In normal mode, the first time I got a Stack Overflow error. The second time it crashed. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 9, 2015 at 19:17
Thanks for trying it out. That being said how would you move that cell value? I'm open to sugestions. — sgp667
– sgp667, Commented Mar 9, 2015 at 19:18

Ron Rosenfeld · Accepted Answer · 2015-03-09 19:24:33Z

1

In your code above, I would change it as below to accomplish the same task, referring to individual cells of the range object:

Series(i).Value2 = Series(i + 1).Value2 Series(i + 1).Value2 = ""

answered Mar 9, 2015 at 19:24

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sgp667 Over a year ago

Actualy I did this because your version produces 'Object Defined Error number 1004'. That being said I absolutelly agree that your and Seteven Matrin's version seems more natural.

Ron Rosenfeld Over a year ago

it works fine here with no errors. Did you define Series the way you did in your original code?

sgp667 Over a year ago

In my original code 'Series' is initialized in a in a follwoinng way 'For Each Series in All_Series.Rows' All_Sereis is initalized with Selection. Nontheles Excel sees Series.Value2(as well as all other relevant arrays in Range) as two dimensional array.

Ron Rosenfeld Over a year ago

That is not helpful. If Series is a range object, then Each Series will represent a row, not a cell.

Steven Martin · Accepted Answer · 2015-03-09 22:21:38Z

Why have you put the () after the value2 ?

This works just fine...

Series(1, i).Value2 = Series(1, i + 1).Value2 Series(1, i + 1).Value2 = ""

sgp667 · Accepted Answer · 2015-03-10 16:28:47Z

I ended up defining another Range inside the problematic one and I used Offset to refernce Cell next to that Range, it not very elegant but it solves the problem.

Dim Series as Range Dim S_Point as Range Dim i as Integer Set Series = Range("A1:B1") i = 1 Set S_Point = Cells( Series.Row, i) S_Point.Value2 = S_Point.Offset(0,1).Value2 S_Point.Offset(0,1).Value2 = ""

Collectives™ on Stack Overflow

VBA move value to adjecent cell

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related