Referencing a cell using a cell from a different sheet

kwaring

New Member
Joined
Mar 14, 2002
Messages
49
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
In answer as to why your code does not work, see this link from earlier tonite:
http://www.mrexcel.com/board/viewtopic.php?topic=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.
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-16 02:46
 
Upvote 0
Hi Dave, sorry, didn't mean to step on you there; I responded before looking for subsequent posts.
 
Upvote 0
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" :wink:
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top