Changing \

[RET79]

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]

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]

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]

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