Use R1C1 to select cell
 

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

Thank you Mike. That worked. Do you know how to identify the row number of
the active cell? I've been trying, but the ROW command seems to escape me.

"Mike" wrote:

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

The following line in the macro wilol return the coilm letter

ColumnLetter = Chr(myvalue + 64)

Mike

"JimK" wrote:

Thank you Mike. That worked. Do you know how to identify the row number of
the active cell? I've been trying, but the ROW command seems to escape me.

"Mike" wrote:

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

Sorry, I mesread you second question. The row number will always be 1 because
that's what the macro does it selects a random column between 1 & 8 and then
selects the cell in row 1 of that column.

Mike

"JimK" wrote:

Thank you Mike. That worked. Do you know how to identify the row number of
the active cell? I've been trying, but the ROW command seems to escape me.

"Mike" wrote:

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

Sorry, I should have clarified: this is a question for a different part of
the macro. If I have an active cell, how do I identify what the row is for
that active cell (unrelated to the question you already helped me with).
Thanks.

"Mike" wrote:

Sorry, I mesread you second question. The row number will always be 1 because
that's what the macro does it selects a random column between 1 & 8 and then
selects the cell in row 1 of that column.

Mike

"JimK" wrote:

Thank you Mike. That worked. Do you know how to identify the row number of
the active cell? I've been trying, but the ROW command seems to escape me.

"Mike" wrote:

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

Use R1C1 to select cell
 

msgbox activecell.row



JimK wrote:

Sorry, I should have clarified: this is a question for a different part of
the macro. If I have an active cell, how do I identify what the row is for
that active cell (unrelated to the question you already helped me with).
Thanks.

"Mike" wrote:

Sorry, I mesread you second question. The row number will always be 1 because
that's what the macro does it selects a random column between 1 & 8 and then
selects the cell in row 1 of that column.

Mike

"JimK" wrote:

Thank you Mike. That worked. Do you know how to identify the row number of
the active cell? I've been trying, but the ROW command seems to escape me.

"Mike" wrote:

Try this, works on the active worksheet.

Sub randomnums()
myvalue = Int((8 * Rnd) + 1)
Cells(1, myvalue).Select
End Sub

Mike

"JimK" wrote:

Hi, I'm trying to program my macro to:
Chose a random number between 1 and 8, then identify the column in my
worksheet that corresponds to that random number (i.e. 4 = column D), then
select the cell in Row 1 in that column.
Any help would be greatly appreciated. Thanks.

--

Dave Peterson