Cell reference as variable?

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Does anyone know if it's possible to set the active cell on one sheet as a variable so that it can be used to select the same cell on another sheet? So that if I select, say, C5 on Sheet1 and then run the macro it will automatically select Sheet2 and cell C5? Both cells would contain no data. I know I can do it if I have the cell reference typed into the cell:
Code:
Dim i As String
i = ActiveCell

Sheets("Sheet2").Activate
Range(i).Select

However, I am unable to see how I can get it to work if the cell on Sheet1 is blank.

I'm sure it's simple but so am I so please help! :confused:
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118
Hi Magriza,

Welcome to the Board. With my limited knowladge i used this in my code to do the trick:

Code:
Sub activate()
Dim i
Dim R
Dim C
i = ActiveCell
R = ActiveCell.Row
C = ActiveCell.Column
Sheets("Sheet2").Select
Cells(R, C).Select
End Sub

I am sure there is a better way to do it but I couldn't find it at the time

Cheers,
Matt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
How about this?
Code:
Dim i As String 
i = ActiveCell.Address

Sheets("Sheet2").Range(i).Select
 

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Thank you to both of you! Worked perfectly. Had to select sheet2 before the range in the second solution before it worked...

Code:
Dim i As String
i = ActiveCell.Address

Sheets("Sheet2").Select
Range(i).Select

I'm going to sacrifice a floppy disc on my mouse mat altar in thanks! :biggrin:
 

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118

ADVERTISEMENT

Briliant

Thanks Norie,

Learning something new everyday!
Cheers,
Matt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Magriza said:
Had to select sheet2 before the range in the second solution before it worked...
What are you actually trying to do?

You shouldn't need to select anything to work with a range/cell.
 

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Sorry, didn't really explain myself well. The second part of your code:

Code:
Sheets("Sheet2").Range(i).Select

Returned the following error:

Run-time error '1004':

Select method of Range class failed.

However, as soon as I instructed the code to select sheet2 first it worked fine!
Code:
Sheets("Sheet2").Select
Range(i).Select

Thanks once again for your help.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Magriza said:
Sorry, didn't really explain myself well. The second part of your code:

Code:
Sheets("Sheet2").Range(i).Select

Returned the following error:

Run-time error '1004':

Select method of Range class failed.

However, as soon as I instructed the code to select sheet2 first it worked fine!
Code:
Sheets("Sheet2").Select
Range(i).Select

Thanks once again for your help.

You cannot select both the sheet and range in the same Select statement. But you can probably avoid selects altogether, as has been mentioned...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,018
Messages
5,834,976
Members
430,331
Latest member
Syed Yasir Hannan

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
Top