Changing \

RET79 · Mar 26th, 2002, 07:28 PM

I wonder if anyone could help me.

I am setting up an input sheet, where the user will type in the cell reference such as B2. However, I have a macro that requires Cells(2,2) rather than B2. I won't go into the reasons why just yet, they aren't relevant just now.

So, currently, instead of letting the user type B2, I have two columns - one for Row number, and the other for Col number like this

Row Col
24 2
35 6
3 13

I will probably let the user type in B2, F67 or whatever in column "A" of a spreadsheet, then have some functions in column B and C to give us the row and column numbers respectively (probably by manipulating text strings etc.)

If anyone can help me out finding such functions then this would be cool. I want the end result to look like this

CellRef Row Col
B2 2 2
D5 5 4
D7 7 4

Where the user enters only the cellRef and the Row& Col are calcualted by formulas on sheet. Thanks.

[ This Message was edited by: RET79 on 2002-03-26 19:23 ]

Dave Hawley · Mar 26th, 2002, 07:34 PM

Hi, Let's assume you have say "B20" typed in range A1

Sub DoIt()
Dim lRow As Long
Dim iCol As Integer
lRow = Range(Range("A1")).Row
iCol = Range(Range("A1")).Column
Cells(lRow, iCol).Select
End Sub

MrExcel · Mar 26th, 2002, 07:37 PM

Great question. You can use two obscure functions to do this:

=CELL("row",B2) will tell you the row number of cell B2.

=Cell("col",B2) will tell you the column number of cell B2.

If you have "B2" stored in another cell, say cell A2, then you have to use the INDIRECT function, like this:
=Cell("row",Indirect(A2))

Bill

RET79 · Mar 26th, 2002, 07:52 PM

Thanks guys, that did the job superbly.

However, it has brought up one more problem. My macro used this before to be a dynamic range

Range([B1], [B1].End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Now, I have 3 columns now like this

CellRef Row Col
B2 2 2
B4 4 2

But now, instead of these being numbers, they are forumlas. For instance, the forumlas in column B are of the form:

=IF(A2="","",CELL("col",INDIRECT(A2)))

in B2 and similarly

=IF(A6="","",CELL("row",INDIRECT(A6)))

in C2.

Now, the problem is that the dynamic range now INCLUDES the cells which contain formulas, which is very annoying. Is there a way of setting the dynamic range so that it only picks up the cells in the column which has a value?

Thanks.

[ This Message was edited by: RET79 on 2002-03-26 19:16 ]

Mar 26th, 2002, 07:37 PM	#3
MrExcel . Join Date: Feb 2002 Location: Akron, Ohio USA Posts: 789	Great question. You can use two obscure functions to do this: =CELL("row",B2) will tell you the row number of cell B2. =Cell("col",B2) will tell you the column number of cell B2. If you have "B2" stored in another cell, say cell A2, then you have to use the INDIRECT function, like this: =Cell("row",Indirect(A2)) Bill __________________ Preview my latest book for Free

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 07:28 PM	#1
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	I wonder if anyone could help me. I am setting up an input sheet, where the user will type in the cell reference such as B2. However, I have a macro that requires Cells(2,2) rather than B2. I won't go into the reasons why just yet, they aren't relevant just now. So, currently, instead of letting the user type B2, I have two columns - one for Row number, and the other for Col number like this Row Col 24 2 35 6 3 13 I will probably let the user type in B2, F67 or whatever in column "A" of a spreadsheet, then have some functions in column B and C to give us the row and column numbers respectively (probably by manipulating text strings etc.) If anyone can help me out finding such functions then this would be cool. I want the end result to look like this CellRef Row Col B2 2 2 D5 5 4 D7 7 4 Where the user enters only the cellRef and the Row& Col are calcualted by formulas on sheet. Thanks. [ This Message was edited by: RET79 on 2002-03-26 19:23 ]

Mar 26th, 2002, 07:34 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi, Let's assume you have say "B20" typed in range A1 Sub DoIt() Dim lRow As Long Dim iCol As Integer lRow = Range(Range("A1")).Row iCol = Range(Range("A1")).Column Cells(lRow, iCol).Select End Sub

Mar 26th, 2002, 07:52 PM	#4
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Thanks guys, that did the job superbly. However, it has brought up one more problem. My macro used this before to be a dynamic range Range([B1], [B1].End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Now, I have 3 columns now like this CellRef Row Col B2 2 2 B4 4 2 But now, instead of these being numbers, they are forumlas. For instance, the forumlas in column B are of the form: =IF(A2="","",CELL("col",INDIRECT(A2))) in B2 and similarly =IF(A6="","",CELL("row",INDIRECT(A6))) in C2. Now, the problem is that the dynamic range now INCLUDES the cells which contain formulas, which is very annoying. Is there a way of setting the dynamic range so that it only picks up the cells in the column which has a value? Thanks. [ This Message was edited by: RET79 on 2002-03-26 19:16 ]