Referencing a cell using a cell from a different sheet

[kwaring]

The cells I am trying to reference are in a sheet called interface. In this example, i am trying to use values in cells E37 and E39 to reference Ax and Hy where x and y are the respective values of cells E37 and E39 in sheet "Interface"... Why doesn't this work or how can i make it work?

Range("A" & [Interface!$E$37] & ":" & "H" & [Interface!$E$39]).Select

Thanks!

[Dave Hawley]

Hi

Try it this way

Range("A" & Sheets("Interface").Range("E37") & ":" _
& "H" & Sheets("Interface").Range("E39")).Select

It will work just so long as you have relavent values in E37 & E39 of the sheet Interface.

[kwaring]

That still doesn't work

[Tom Urtis]

In answer as to why your code does not work, see this link from earlier tonite:
http://www.mrexcel.com/board/viewtop...2503&forum=2&2

In answer to your second question about how to make it work, try this modification of your code. It worked when I tested it:

Sub SelectRangeTakeTwo()
On Error GoTo e
Range("I" & Sheet2.[E37] & ":" & "I" & Sheet2.[E39]).Select
Exit Sub
e:
MsgBox "Place a valid numeral in E37 or E39" _
& vbCrLf & "of your Interface worksheet.", _
48, "Can't determine range"
End Sub

Very important, this will work only if your Interface sheet is VBA worksheet object #2 of your workbook. Press Alt+F11 to get into the Visual Basic Editor, and see the pane on the left entitled Project - VBAProject. If you don't see it, press Ctrl+R.

Then, find your workbook name, in the syntax VBAProject (WorkbookName), double click to open it, and then open the folder titled "Microsoft Excel Objects". My guess is you will a sheet name, in the syntax Sheet#(Interface), where # is the sheet object number for your Interface sheet. If that number is not 2, then modify this code by plugging in whatever number that Interface sheet is.

[Dave Hawley]

kwaring, instead of just saying "It still doesn't work" how about helping us help you by:

1 Telling us the values in the cells E37 and E39

2. Tell us what the error is you are getting.

As I intitially said:

"It will work just so long as you have relavent values in E37 & E39 of the sheet Interface"



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-16 02:46 ]

[Tom Urtis]

Hi Dave, sorry, didn't mean to step on you there; I responded before looking for subsequent posts.

[Dave Hawley]

Hi Tom

No no, not at all! Just very hard to help when the only response you get is "it still doesn't work" Maybe I should just respond with" Well it does for me"

[kwaring]

That still doesn't work... i checked to make sure my interface sheet is sheet2, it isn't so i changed everything to sheet1 but there is still a problem when trying to run the code. Gives the following error:
Select method of range class failed.

[Tom Urtis]

It worked for me, please copy and post your code so we can fix what's ailing it.

[kwaring]

lol... sorry about the "it still doesn't work", what can i say, it's very late! anyways i have tried many values for E37 and E39 all integers above 0. thanks for all the help!