0

In cell A1, there is the string content of "B3", which is the address of another cell. The content of cell A1 changes based upon other user actions. I want to get the content of A1 to be used in an Excel 2013 VBA macro Range(?).Activate command to make cell B3 the active cell. I've searched, Googled, tried various approaches -- all to no success. Any help is greatly appreciated.

GeorgeInNC
  • 35
  • 1
  • 9
  • `Range(Range("A1").Value).Select` – chris neilsen Feb 15 '15 at 17:32
  • Thanks, Chris, that worked like a charm. How can I use this approach to identify a range of cells, say B3 to B6, with the value of "B3" in cell A1 and the value of "B6" in cell A2? I tried Range(Range("A1").Value:Range("A2").Value).Select, which failed miserably. – GeorgeInNC Feb 17 '15 at 16:53
  • Great! Once again, you've solved my problem. Thank you. – GeorgeInNC Feb 19 '15 at 14:55
  • I should have said: "... you've solved THAT problem." Here's the big picture: I'm trying to copy a specific range of cells from one worksheet into the active worksheet, both in the same workbook. The code of Range((Range("A1").Value), (Range("A2").Value)).Select works as expected for the cells in the active worksheet. – GeorgeInNC Feb 19 '15 at 16:23
  • When I try using Sheets("MDs Data").Range((ActiveSheet.Range("A1").Value), (ActiveSheet.Range("A2").Value)).Select to select the range of cells in the "MDs Data" worksheet, which is different from the active worksheet, I get a Run-time error "1004": Select method of Range class failed. The research I've done indicates that the Sheets("MDs Data") should direct the Range selection to that worksheet. Ideas? – GeorgeInNC Feb 19 '15 at 16:24
  • Firstly you should edit this additional info into your question. Secondly, you can only select a cell on the active sheet. Thirdly, you don't need to and shouldn't select before copying. [see this](http://stackoverflow.com/a/10717999/445425) – chris neilsen Feb 19 '15 at 18:24
  • And please don't turn this into a [camelleon question](http://meta.stackexchange.com/questions/43478/exit-strategies-for-chameleon-questions) – chris neilsen Feb 19 '15 at 18:27

1 Answers1

0

I think this is what you need (change the name of the sheet object):

Sheet1.Range(Sheet1.Range("a1").Value2).Activate

Or (if you want to use active sheet)

ActiveSheet.Range(ActiveSheet.Range("a1").Value2).Activate
BrakNicku
  • 5,935
  • 3
  • 24
  • 38