Combining the cells and range properties

andygis

New Member
Joined
Sep 11, 2006
Messages
5
Hi there, a question from a new subscriber...

Selecting on A1 notation is fine but using...

Range(Cells(2, 1), Cells(745, 13)).Select

Fails in the programme but works in the immediate window. Am I missing something obvious?

I think I have tried all the options of naming the range, activesheet.range etc

Any tips much appreciated.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, andygis
Welcome to the Board !!!!!

if this code is in a worksheetmodule
if within the module you selected another sheet
this code will bug

because
Range... refers to the range of the sheet where the module is attached to

remark: this code would work in a normal module
then Range... is refering to the activesheet

was this the problem ?

else please tell us more about your code and circumstances

kind regards,
Erik
 

andygis

New Member
Joined
Sep 11, 2006
Messages
5
Combining the cells and range properties continued

Thanks for the quick reply Erik

The code is located in one spreadsheet, which activates another and then performs a search. On the basis of that search a region will be selected, but this will vary depending on the spreadsheet open.

The code and a few attempts to get to grips with it:

Workbooks(strOpenFileName).Activate 'opens another worksheet to search

FindDataInRange ActiveSheet.Range("E:E"), intT 'passes search _
area and what to search for to a function
MsgBox ActiveCell.Row & ", " & ActiveCell.Column 'returns row, col _
of first instance of find object

MsgBox ActiveSheet.Name 'checks what sheet is active
ActiveSheet.Range("a56:c68").Select 'test select - works ok
MsgBox "" 'pause
ActiveSheet.Cells(3, 5).Select 'this reference works
MsgBox "" 'pause
ActiveSheet.Range(Cells(6, 3), Cells(9, 9)).Select ' test cells _
select - does not work

using Workbooks(strOpenFileName).Range("a56:c68").Select - also does not work

Thanks for any advice,

Andy
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
OK, my post appeared to be "profetic" :LOL: talking about sheetevents

this is not correct
ActiveSheet.Range(Cells(6, 3), Cells(9, 9)).Select
the range "as a whole" is now refering to the sheet
but the parts like Cells(6, 3) are not
you need
Activesheet before each "member"
ActiveSheet.Range(ActiveSheet.Cells(6, 3), ActiveSheet.Cells(9, 9)).Select
or easier to edit
Code:
With ActiveSheet
.Range(.Cells(6, 3), .Cells(9, 9)).Select
End With
typed off-hand, typos possible

remark: selecting cells is not needed for most of the actions

greetings from Belgium,
Erik
 

andygis

New Member
Joined
Sep 11, 2006
Messages
5
Excellent, thanks Erik, if only I had thought to post a message earlier!

Andy
 

Forum statistics

Threads
1,136,350
Messages
5,675,246
Members
419,557
Latest member
razlevav

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