Another syntax question (different from earlier post)

[Guest]

All:

Another general question about syntax. This one deals with .Range vs .Cells.

I know that .Cells will accept two variables, e.g. mySheet.Cells(myRow,
myCol).

Can .Range do the same? If so, how would I write a .Range to accept a
variable for the column AND and both the column and the row? can this be
done? See below.

Thanks,
MARTY

mySheet.Range("A" & myRow) 'I know this works OK.

'Problem 1
'varying the column designation with a constant row

mySheet.Range(myCol & "1") 'this doesn't work I don't believe, even when
myRow is a capital letter.

'Problem 2
'varying both the colum and the row
mySheet.Range(myCol & myRow) 'I know this doesn't work even when myRow is a
capital letter and myRow is a positive integer. I think I need some double
quotes but I don't know where.

 

[Guest]

CORRECTION: In the two "problems" after my display name, I meant to say the
following:
Problem 1: ...even when myCol is a capital letter

Problem 2: ...even when myCol is a capital letter and myRow is a positive
integer.

Sorry for the confusion.

MARTY

 

[Tom Ogilvy]

Set MySheet = Activesheet
mycol = "A"
myRow = 1
msgbox MySheet.Range(mycol & myrow).Address

works fine. For RANGE, myCol must be letters, not numbers.

Also

Set MySheet = Activesheet
mycol = "A"
myRow = 1
mycol1 = "Z
myRow1 = 26
msgbox MySheet.Range(mycol & myrow & ":" & myCol1 & myrow1).Address

will work.


If you want numbers only, why not use CELLS

 

[Guest]

I see my problem now. I was missing the ":" separator.

Thanks for the great help as usual, Tom.