I want to have the contents of one cell equal active cell

[hedgeman50]

How do I have the contents of a cell equal the contents of the currently active cell (i.e., the cell currently selected)? I have a long list of stock symbols (about 3,000) in column 3. I want to be able to simply hit the down arrow key to get to the next cell and have the cell K3 always have in it the contents of the active cell.

Example: If the active cell is A1 (and let's say that A1 has the symbol MSFT in it), then I want K3 to have MSFT in it. If I move to cell A6 (with the arrow key or with the mouse) and it has INTC in it, then I want K3 to have INTC in it.

Thank you very much for any help!

 

[Yogi Anand]

THANKS Tom

Regards!

Yogi

 

[Evan Galvanek]

THANKS Tom

Regards!

Yogi

You guys know how to make the "dynamic cell" we're creating equal to any cell in ANY worksheet rather than the current?

Thanks in advance guys.

 

[pjmorris]

I was directed to this from another forum and hope you don't mind me chipping in. I can see how it works, but for me there are a couple of disadvantages. The 'push' nature of this solution causes two problems:

1. The value of the activecell can't be used in an equation.
2. Any changes to the layout of the spreadsheet need the macro to be manually updated.

I would offer the following as a solution to both:

In the code associated with Sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
cellvalue = Target
Range("A1:G11").Dirty
​

End Sub
​

The Range.Dirty method should be changed to make sure it includes the cells that contain the ActiveCellValue() function.

In Module1:

Public cellvalue 'declares public storage for the value of the cell under the cursor

Function ActiveCellContent()

ActiveCellContent = cellvalue​

End Function

​

The function can then be used to return the value of the Activecell, the Range.Dirty method in the worksheet_selectionchange macro will ensure the cells get recalculated (but make sure the range is sufficiently broad). This allows the value at the active cell to be used as part of an equation or equations.

Hope this helps.

Regards

Peter

 

[ydnar185]

Is there any way to make the cell equal to the address of the active cell? So the cell value would be A1 for example?

 

[priuscomet]

You guys know how to make the "dynamic cell" we're creating equal to any cell in ANY worksheet rather than the current?

Thanks in advance guys.

I'm curious about this too. Is it possible to set the value to an active cell from another sheet in the workbook?

Thanks!