Referencing a cell as a row number
 

I need to 1. determine the last row of a column (This row varies each month.)
2. select a cell using this last row number.

I have written the following which solves number 1. above:
Range("B3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "X"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Decide", RefersToR1C1:="=Sheet1!C2"
Range("Z1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=Match(""X"",Decide,0)"

but when I try to reference the cell Z1 as the row number, I get a syntax
error.
How do I select a cell in column T and the last row number found as the
value in cell Z1?

Referencing a cell as a row number
 

Cells(Range("Z1").Value, "T").Select
--
Gary''s Student - gsnu200803

Referencing a cell as a row number
 

Range("T" & Range("Z1").Value).Select

A comment about another part of your code:

Assuming you have data in B1 to B10, but B5 is blank
Range("B3").End(xlDown).Select will find B4
Range("B65536").End(xlUp).Select will find B10

I don't know your data, so this may not make any difference to you.

--
Ian
--
"iashorty" wrote in message
...
I need to 1. determine the last row of a column (This row varies each
month.)
2. select a cell using this last row number.

I have written the following which solves number 1. above:
Range("B3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "X"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Decide", RefersToR1C1:="=Sheet1!C2"
Range("Z1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=Match(""X"",Decide,0)"

but when I try to reference the cell Z1 as the row number, I get a syntax
error.
How do I select a cell in column T and the last row number found as the
value in cell Z1?

Referencing a cell as a row number
 

If all you want to know is the next available row in col B then
Sub lastrowincolb()
MsgBox Cells(Rows.Count, "b").End(xlUp).Row+1
or
range("z1").value=Cells(Rows.Count, "b").End(xlUp).Row+1
End Sub

-
Don Guillett
Microsoft MVP Excel
SalesAid Software

"iashorty" wrote in message
...
I need to 1. determine the last row of a column (This row varies each
month.)
2. select a cell using this last row number.

I have written the following which solves number 1. above:
Range("B3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "X"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Decide", RefersToR1C1:="=Sheet1!C2"
Range("Z1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=Match(""X"",Decide,0)"

but when I try to reference the cell Z1 as the row number, I get a syntax
error.
How do I select a cell in column T and the last row number found as the
value in cell Z1?

Referencing a cell as a row number
 

Thanks, these are great. I didn't have to worry about not all of the cells in
Column B being filled. They are. But, thank you for the warning.


"IanC" wrote:

Range("T" & Range("Z1").Value).Select

A comment about another part of your code:

Assuming you have data in B1 to B10, but B5 is blank
Range("B3").End(xlDown).Select will find B4
Range("B65536").End(xlUp).Select will find B10

I don't know your data, so this may not make any difference to you.

--
Ian
--
"iashorty" wrote in message
...
I need to 1. determine the last row of a column (This row varies each
month.)
2. select a cell using this last row number.

I have written the following which solves number 1. above:
Range("B3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "X"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Decide", RefersToR1C1:="=Sheet1!C2"
Range("Z1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=Match(""X"",Decide,0)"

but when I try to reference the cell Z1 as the row number, I get a syntax
error.
How do I select a cell in column T and the last row number found as the
value in cell Z1?

Referencing a cell as a row number
 

Glad to help.

Thanks for the feedback.

--
Ian
--
"iashorty" wrote in message
...
Thanks, these are great. I didn't have to worry about not all of the cells
in
Column B being filled. They are. But, thank you for the warning.


"IanC" wrote:

Range("T" & Range("Z1").Value).Select

A comment about another part of your code:

Assuming you have data in B1 to B10, but B5 is blank
Range("B3").End(xlDown).Select will find B4
Range("B65536").End(xlUp).Select will find B10

I don't know your data, so this may not make any difference to you.

--
Ian
--
"iashorty" wrote in message
...
I need to 1. determine the last row of a column (This row varies each
month.)
2. select a cell using this last row number.

I have written the following which solves number 1. above:
Range("B3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "X"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Decide", RefersToR1C1:="=Sheet1!C2"
Range("Z1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=Match(""X"",Decide,0)"

but when I try to reference the cell Z1 as the row number, I get a
syntax
error.
How do I select a cell in column T and the last row number found as the
value in cell Z1?