Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Dynamic cell address?

This is a discussion on Dynamic cell address? within the Excel Questions forums, part of the Question Forums category; I've stumped myself yet again. I have a cell named "bounce" that will provide the column, and the row I ...

  1. #1
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default Dynamic cell address?

    I've stumped myself yet again.

    I have a cell named "bounce" that will provide the column, and the row I can determine using Activecell.row. The column will constantly be changing as I will be adding columns now and then, hence the named range.

    What I can't figure out is how to put them together to make a usable cell address.

    I only need to come up with this so I can use an end(xltoleft) command to get to the last row of my data--I tried doing this the "easy" way of just whatever row has the currently active cell and doing an xltoright, but there are some blanks in the data here and there and it buggers it all up.

    Any thoughts?

    Thanks

    (Note: I most likely won't be able to reply until Dec. 1 when I'm back at work with the file in front of me)
    Kristy

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: Dynamic cell address?

    Hi,

    Not sure I understand exactly what you are asking.

    Code:
    Sub tester()
    Dim BounceRow As Long
    Dim BounceCol As Long
    
    With Range("Bounce")
        BounceRow = .Row
        BounceCol = .Column
    End With
    
    MsgBox Cells(BounceRow, BounceCol).Address
    
    End Sub
    Bye,
    Jay

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Dynamic cell address?

    Howdy Pook,

    Quote Originally Posted by Von Pookie
    I only need to come up with this so I can use an end(xltoleft) command to get to the last row of my data--I tried doing this the "easy" way of just whatever row has the currently active cell and doing an xltoright, but there are some blanks in the data here and there and it buggers it all up.

    Any thoughts?
    {snip}
    Yes,

    Nothing like posting and running eh?

    Sub dhfjdsh()
    MsgBox [bounce].End(xlToLeft).Address
    End Sub


    Did you mean last column?

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: Dynamic cell address?

    Quote Originally Posted by NateO
    Nothing like posting and running eh?
    Posting with profanity, no less.
    Maybe she's away home to cook the turkey for tomorrow.

  5. #5
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default

    Noooo, I was away home to do nothing (we went out and rented video games) and then go to my auntie's Thursday where she cooked the turkey. So nya

    Anyway.

    Nate, that seems to do what I want, but will that work for each respective row? There's about 1500 of them. If it returns the last column, I think that would be ok since before I want that part to run I'll add a new column but it won't be entirely blank by then and it should catch that one.

    Basically, I'm adding a column and need the new info to go there. But if I use an End(xltoright) from the number I just checked, it gets thrown off when there are blanks in the row. There is a separate table of information to the right of my data, so I'm trying to "go backwards" from there and do the xltoleft.

    God. Does any of that make sense? It's so hard to explain what's there.
    Kristy

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Hello again Pook,
    Quote Originally Posted by Pook
    God. Does any of that make sense? It's so hard to explain what's there.
    {snip}
    Not really my child, but here goes nothing...

    Quote Originally Posted by Pook
    Nate, that seems to do what I want, but will that work for each respective row? There's about 1500 of them. If it returns the last column, I think that would be ok since before I want that part to run I'll add a new column but it won't be entirely blank by then and it should catch that one.
    {snip}
    Ce qui?

    I think you want to get rid of this named range business and use something like the following:

    Sub URange()
    With Sheets(1)
    ****.UsedRange
    ****With .UsedRange
    ********MsgBox .Item(.Rows.Count, .Columns.Count).Address
    ****End With
    End With
    End Sub


    You can refer to the Range object with the returned string.

    Hope this helps.

    Edit: No need to generate a string, just set the object:

    Sub URange2()
    Dim myRng As Range
    With Sheets(1)
    ****.UsedRange
    ****With .UsedRange
    ********Set myRng = .Item(.Rows.Count, .Columns.Count)
    ****End With
    End With
    Debug.Print myRng.Address(False, False)
    End Sub


    This does not concern itself with blank row or column cells. Just grabs the bottom right of your sheet's data.

  7. #7
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default

    If the location of the table to the right is fixed (or the range is named), why not first-table-cell-in-current-row.End(xltoleft)?

    Also, what is the intent? Sometimes, all you need is a named formula that adjusts itself as data are added to a list.

    Quote Originally Posted by Von Pookie

    Basically, I'm adding a column and need the new info to go there. But if I use an End(xltoright) from the number I just checked, it gets thrown off when there are blanks in the row. There is a separate table of information to the right of my data, so I'm trying to "go backwards" from there and do the xltoleft.

    God. Does any of that make sense? It's so hard to explain what's there.

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Dynamic cell address?

    This will find the last column despite intervening blank columns:

    Sub colSelect()
    Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)).Select
    End Sub


    This fails:

    Sub colSelect_2()
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    End Sub


    HTH

    Mike

  9. #9
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default

    Quote Originally Posted by tusharm
    If the location of the table to the right is fixed (or the range is named), why not first-table-cell-in-current-row.End(xltoleft)?
    Because I'm just playing this by ear and don't necessarily have any clue as to what I'm doing. What little I know about macros is self-taught from messing around with them and this board. That's about it.

    Also, what is the intent? Sometimes, all you need is a named formula that adjusts itself as data are added to a list.
    um.... Don't get that at all. Not in this situation, at any rate.

    Ok. I'm tired of this thing anyway, so here we go.
    Here's the file I'm working with, cut down to the only sheets I'm actually using (around 480k zipped):

    http://home.insightbb.com/~ksharpe/misc/test4.zip

    Please note, the macros are an utter mess, I know. That's why this is one of my "tester" files.

    Macros: All I have been messing with are in the modules labeled Module1, Module2 and Format_Codes

    The imported information from Sheet1 needs to match the information on database2. I've figured out a way to populate the cells (Source_Format and Dest_Format) to try to simplify checking one sheet against the other.

    The other table of information currently starts at cell AM9 and is the same length as the table on the left, to which I will be adding the new column of information at the end. My original thought was that I could somehow find a way to find out what column that other table is in after adding a row, I could build a dynamic address where the column would be constant, but the row number would change for each row that is being currently checked using the for each cell...thingy and use that cell reference to do the xltoleft from to hit my new column.

    I was originally trying to simply have another if statement in there saying if after the xltoright, it was in columns D-F, to then do another one. But that works fine until there's a blank somewhere in the row of actual data.

    Sigh.

    Be thankful...that's not even the hard part, either

    Any better? I'll go embarrass myself elsewhere now
    Kristy

  10. #10
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default

    If 'bounce' will always be a name that applies to a cell in the first column of the table that currently starts in AM, use
    Code:
    Option Explicit
    
    Sub testIt()
        With ActiveSheet
        MsgBox .Cells(ActiveCell.Row, .Range("bounce").Column).End(xlToLeft).Address
            End With
        End Sub
    or
    Code:
    Sub test2()
        Dim A_Cell_In_Last_Column_Of_Table1 As Range
        With ActiveSheet
        Set A_Cell_In_Last_Column_Of_Table1 = _
            .Cells(ActiveCell.Row, .Range("bounce").Column) _
                .End(xlToLeft)
            End With
        MsgBox A_Cell_In_Last_Column_Of_Table1.Address
        End Sub
    When a new column in inserted before the table that currently starts in AM, XL will update the definition of 'bounce.' So, the above code will continue to work irrespective of how many columns you add. Of course, there is the 256 limit, but that is a different matter.

    Quote Originally Posted by Von Pookie
    Quote Originally Posted by tusharm
    If the location of the table to the right is fixed (or the range is named), why not first-table-cell-in-current-row.End(xltoleft)?
    Because I'm just playing this by ear and don't necessarily have any clue as to what I'm doing. What little I know about macros is self-taught from messing around with them and this board. That's about it.

    Also, what is the intent? Sometimes, all you need is a named formula that adjusts itself as data are added to a list.
    um.... Don't get that at all. Not in this situation, at any rate.

    Ok. I'm tired of this thing anyway, so here we go.
    Here's the file I'm working with, cut down to the only sheets I'm actually using (around 480k zipped):

    http://home.insightbb.com/~ksharpe/misc/test4.zip

    Please note, the macros are an utter mess, I know. That's why this is one of my "tester" files.

    Macros: All I have been messing with are in the modules labeled Module1, Module2 and Format_Codes

    The imported information from Sheet1 needs to match the information on database2. I've figured out a way to populate the cells (Source_Format and Dest_Format) to try to simplify checking one sheet against the other.

    The other table of information currently starts at cell AM9 and is the same length as the table on the left, to which I will be adding the new column of information at the end. My original thought was that I could somehow find a way to find out what column that other table is in after adding a row, I could build a dynamic address where the column would be constant, but the row number would change for each row that is being currently checked using the for each cell...thingy and use that cell reference to do the xltoleft from to hit my new column.

    I was originally trying to simply have another if statement in there saying if after the xltoright, it was in columns D-F, to then do another one. But that works fine until there's a blank somewhere in the row of actual data.

    Sigh.

    Be thankful...that's not even the hard part, either

    Any better? I'll go embarrass myself elsewhere now

Page 1 of 2 12 LastLast

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
  •  


DMCA.com