Excel VBA - Use value in cell as address to target another cell

Question

this is my first post!

I have N11 which displays a cell location that I want to place string variable x in, so when N11 reads G2, I need to enter x into G2.

I have been unsuccessful so far with:

   y = Worksheets("Sheet2").Cells(11, "N").Value
Range(y) = x

Thanks in advance!

https://support.office.com/en-us/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261 — Robin Mackenzie, Mar 10 '17 at 04:14
http://stackoverflow.com/questions/33305508/how-to-code-excel-vba-equivalent-of-indirect-function — Robin Mackenzie, Mar 10 '17 at 04:15
Just to clarify: N11 contains a cell address that you want to programmatically update with the value of (an elsewhere defined) variable "x"? — Mark Fitzgerald, Mar 10 '17 at 04:17
@Mark Fitzgerald N11 contains the address to which I want variable x entered. So When N11 shows $G$2 vairable x should be entered in G2. EDIT: so N11's code is: =CELL("address",INDEX(G:G,MATCH(Sheet1!C8,A:A))) — Harry M, Mar 10 '17 at 04:31
Editing your question down to the minimum required to convey what you are looking for, what you have (i.e. minimal but complete code) and where you are stuck will get much more attention and possible answers. Take a look at [mcve]. — Mark Fitzgerald, Mar 10 '17 at 04:46
@Mark Fitzgerald I have cut down my question to hopefully clear things up, thanks for your input! — Harry M, Mar 10 '17 at 05:04
You have been successful with... great then, is there still a question? — A.S.H, Mar 10 '17 at 05:21
Good result! Welcome to SO. Good luck with your journey and learning. — Mark Fitzgerald, Mar 10 '17 at 05:48

Harun24hr · Accepted Answer · 2017-03-10T05:16:23.983

0

Try following sub.

Sub PutValue()
Dim x As Date
x = #3/10/2017#

    Range(Range("N11").Value).Value = x

End Sub

--------------------------- Edit ----------------------

Sub PutValue()

    Range(Sheet2.Range("N11").Value).Value = "x"

End Sub

edited Mar 10 '17 at 05:16

answered Mar 10 '17 at 04:25

Harun24hr

You can use any data type for `x` rather than `date` type. Test and provide feedback. – Harun24hr Mar 10 '17 at 04:27
Hi, I tried the sub without success, I realised my question had gotten a bit confusing so I have rewritten is to hopefully clarify. – Harry M Mar 10 '17 at 05:09
@HarryM So, you have 'G2` in cell `N11` and you want to put `x` in `G2` by pragmatically, right? – Harun24hr Mar 10 '17 at 05:12
Now all you need to do is accept the answer by clicking the tick and give him an up-vote for his work. – Mark Fitzgerald Mar 10 '17 at 05:59
@Mark Fitzgerald I found that my code was working fine but as it originated from sheet 2 of my workbook and N11 simply gives "$A$1", the data was output to that location in sheet 1 which is covered by an image. Thank you both for your help! – Harry M Mar 14 '17 at 00:39

1 Answers1