HI, I am new to excel and this group so treat me kindly :-)

I want to add some VB code to transfer the cell location on one sheet to
another.

That is on sheet1 in cell A1 via VB code I want to insert in that cell
"=sheet2!c3" so that A1 is always the value of sheet2!c2.

I am using a ListBox to get a value of a list and put it in 'Reff'.

The list in sheet 'Constants' is range A3 to A10, so a 3 in Reff points to
Constants!A5.

In 'Selection.Cells(1).Offset(0, 1)' I want to end up with "=Constants!A5".
How do I get there from here?

Thanks for any help you can give me.

Also can you reccomend a good book (no, not Harry Potter....)

---------------code snip --------------------

Range("Reff") = lstSelection.ListIndex
Selection.Cells(1) = lstSelection.Text 'this works fine
Selection.Cells(1).Offset(0, 1) = 'what do I put here???
---------------------end snip



Cheers
Brian
___________________________________
Expressive Systems.
www.expressivesystems.com

Brian,

Try something like

Worksheets("Sheet2").Range("C3").Value = _
Worksheets("Sheet1").Range("C1").Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Brian" wrote in message
. com...
HI, I am new to excel and this group so treat me kindly :-)

I want to add some VB code to transfer the cell location on one
sheet to
another.

That is on sheet1 in cell A1 via VB code I want to insert in
that cell
"=sheet2!c3" so that A1 is always the value of sheet2!c2.

I am using a ListBox to get a value of a list and put it in
'Reff'.

The list in sheet 'Constants' is range A3 to A10, so a 3 in
Reff points to
Constants!A5.

In 'Selection.Cells(1).Offset(0, 1)' I want to end up with
"=Constants!A5".
How do I get there from here?

Thanks for any help you can give me.

Also can you reccomend a good book (no, not Harry Potter....)

---------------code snip --------------------

Range("Reff") = lstSelection.ListIndex
Selection.Cells(1) = lstSelection.Text 'this works fine
Selection.Cells(1).Offset(0, 1) = 'what do I put
here???
---------------------end snip



Cheers
Brian
___________________________________
Expressive Systems.
www.expressivesystems.com

Hi Brian,

Following should do the job :
Worksheets("Sheet1").Range("A1").Formula = "=Sheet2!C2"

HTH
Cheers
Carim