Enter data in cell referenced in a cell

[Brian]

In cell A2 I have determined the number 3
In A3 I have created a cell reference
Scoresheet1!AD3
Which references a cell on another sheet,
I am looking for the VBA code which will enable me to place the number in cell A2 in the cell rerenced in cell A3
I am using Excel 2007
appreciate any help.

 

[GS]

Brian submitted this idea :

In cell A2 I have determined the number 3
In A3 I have created a cell reference
Scoresheet1!AD3
Which references a cell on another sheet,
I am looking for the VBA code which will enable me to place the number in
cell A2 in the cell rerenced in cell A3 I am using Excel 2007
appreciate any help.

This presumes the sheet containing the target cell (A2) is the active
sheet, AND the cell containing the ref (A3) is the active cell.

Dim vSource As Variant
vSource = Split(ActiveCell.Value, "!")
ActiveCell.Offset(0, -1).Value = _
Sheets(vSource(0)).Range(vSource(1)).Value

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

In the case of a sheetname having spaces, revise the appropriate line
as follows:

vSource = Split(Replace(ActiveCell.Value, "'", ""), "!")

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Brian]

Brian submitted this idea :

This presumes the sheet containing the target cell (A2) is the active
sheet, AND the cell containing the ref (A3) is the active cell.

Dim vSource As Variant
vSource = Split(ActiveCell.Value, "!")
ActiveCell.Offset(0, -1).Value = _
Sheets(vSource(0)).Range(vSource(1)).Value

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you this works perfectly. Your assistance is much appreciated

 

[GS]

Brian was thinking very hard :

Thank you this works perfectly. Your assistance is much appreciated

You're welcome! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion