How do I do this (I am new to Excel)

A

ashnook

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
 
D

davesexcel

That looks like a great job for the macro recorder
Select the cell one cell above the point you want to start the macro
Hit the macro recorder and just do exacactly what you described, when
you are finished doing the actions select the cell where you want it
to be when the macro finishes.
 
P

Peter Aitken

ashnook said:
Also can you reccomend a good book (no, not Harry Potter....)
Click to expand...

For Excel programming I immodestly recommend my own book Excel Programming
Weekend Crash Course. For a more general reference I don;t think you can
beat the Excel Bible.
 
A

ashnook

Peter Aitken said:
For Excel programming I immodestly recommend my own book Excel Programming
Weekend Crash Course. For a more general reference I don;t think you can
beat the Excel Bible.
Click to expand...
Thanks Peter, I will look into it, in the mean time how do I do what I want
to do?

Cheers, Brian
 
Top