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.

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

"Marty" wrote:

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.

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

--
Regards,
Tom Ogilvy

"Marty" wrote in message
...
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.

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

Thanks for the great help as usual, Tom.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Marty" wrote in message
...
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.