Combining the cells and range properties

[andygis]

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.

 

[erik.van.geit]

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]

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]

OK, my post appeared to be "profetic" 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

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]

Excellent, thanks Erik, if only I had thought to post a message earlier!

Andy