Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Changing \

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    864
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    View a collection of recent Excel articles in the Excel Daily News

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •