Issues using VLOOKUP within macros

Hi,

I am fairly new to VBA so please bear with me. I am trying to reformat data in the way displayed in the attached file. I am trying to go about this by looping through each of the 500 stocks and doing VLOOKUPs on each of the master index entries on the far left, as seen on the tab "Desired Code to automate"

I have been struggling trying to figure out the correct way to get this done in VBA, the relative/absolute indexing is not working out for me. I would like to be able to do something like

Please Login or Register  to view this content.

I'm aware that this is quite possibly the ugliest code you've ever seen, and for that I am very sorry. That being said I am struggling to get things done efficiently since there are some points I don't understand. If someone could explain or point out a good reference on indexing that would be much appreciated. For example things like RC[3 - 2] don't work? Is this just not supported? Also is the syntax " & i & " the only way to use a variable for indexing? Is there a way to check what my active cell is when I'm debugging? Also if I wrap the VLOOKUP in an IFERROR() statement in excel this seems to work fine but when I throw it in a macro it acts up, i.e. I get a runtime error '1004'.

Note: In writing this I realized if I replace A3 with R3C1 that the initial part works, that being said is there no way to use the A# notation?

Thanks a bunch.

-Matt