VBA/Excel Dynamic cell reference

July 21, 2007 at 10:26:41
Specs: XP pro, AMD64
I'm trying to reference a alue that is stored in a cell.

Cell "S1" has the value "J2" stored in it.

in VBA I'm using this code:

cell = Sheets("Form").Range("S1")
Sheets("Archive").Range(cell).Paste

but it isn't working. How do you do this?



See More: VBA/Excel Dynamic cell reference

Report •


#1
July 21, 2007 at 11:37:05
What are you attempting? Do you want to copy the value from cell Form.S1 to cell Archive.S1?

Sheets("Archive").Range("S1").Formula = Sheets("Form").Range("S1").Formula


Report •

#2
July 21, 2007 at 11:46:00
cell "S1" is essentially a *pointer. In this example is contains the value "J2", which is where I want to paste my data.

the value in "S1" is determining where the number in "E15" is going.("J2")


Report •

#3
July 21, 2007 at 11:52:49
So, do you want it to grab the "pointer," make a new "pointer," or the final value?

Report •

Related Solutions

#4
July 21, 2007 at 12:12:56
"S1" is pointing to the cell where the final value is placed.

"E15" has the value 1.
"S1" has the value "J2".
I want "J2" to have the value of 1.

But the value of "S1" changes and VBA won't let me use the code in my first post.


Report •

#5
July 21, 2007 at 12:33:29
Of course it doesn't let you use your code; the flow of logic is incompatible with VBA. I'm surprised it actually compiled. After all, after I read it, I had to ask you what you wanted.

This line (It's really one line; I blame the line wrapping, myself.) will copy whatever is displayed in the cell "S1" (and not the pointer itself):
Sheets("Archive").Range("J2").Formula = Sheets("Form").Range("S1").Value

If you wanted the pointer, I'd just drop the VBA attempt and change J2's formula to read:
=Form!$S$1


Report •

#6
July 21, 2007 at 13:07:37
But it won't always point to "J2". That's the problem.

Report •


Ask Question