Moving shape based on Cell Value

[gmedalla999]

Hello all,

I am very new to the forum and very new with the use of Macro as well. I wish to be able to move an object to a certain cell (using .top and .left). It is important the location be referenced to the cell versus increment or numerical value. Below is the sample of my code but I could not make it work. Cell I1 is the input cell where I will type which cell the object will go to, i.e. Cell J10? I would greatly appreciate any help I could get. thanks

Sub ShiftShape()

Dim oShape As Shape

Set oShape = ActiveSheet.Shapes("Flowchart: Decision 3")


Dim oCell As Range

Set oCell = ActiveSheet.Range("I1").Value

'the value of Range("I1) will be a text saying which cell the shape will go to.


oShape.Top = oCell.Top
oShape.Left = oCell.Left


End Sub

 

[Alan_P]

Hi gmedalla999,

Welcome to the forum

Try;

Sub ShiftShape()
Rng = ActiveSheet.Range("I1").Value
ActiveSheet.Shapes("Flowchart: Decision 3").Top = Range(Rng).Top
ActiveSheet.Shapes("Flowchart: Decision 3").Left = Range(Rng).Left
End Sub

Hope this helps,
Cheers,
Alan.

 

[FormR]

Hi, welcome to the board:

You could try changing the "SetOcell..." line to this:

Set oCell = Range(Range("I1").Value)

 

[gmedalla999]

Hi, welcome to the board:

You could try changing the "SetOcell..." line to this:

Set oCell = Range(Range("I1").Value)

It worked PERFECTLY!!!! THANK YOU very much

 

[gmedalla999]

Hi gmedalla999,

Welcome to the forum

Try;

Sub ShiftShape()
Rng = ActiveSheet.Range("I1").Value
ActiveSheet.Shapes("Flowchart: Decision 3").Top = Range(Rng).Top
ActiveSheet.Shapes("Flowchart: Decision 3").Left = Range(Rng).Left
End Sub

Hope this helps,
Cheers,
Alan.

Thank you Allan this works quite well too. i will use it as well