Dynamic cell address?

[Von Pookie]

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)

 

[Jay Petrulis]

Hi,

Not sure I understand exactly what you are asking.

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

 

[NateO]

Howdy Pook,

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?

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> dhfjdsh()
MsgBox [bounce].End(xlToLeft).Address
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Did you mean last column?

 

[Mark O'Brien]

Nothing like posting and running eh?

Posting with profanity, no less.
Maybe she's away home to cook the turkey for tomorrow.

 

[Von Pookie]

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.

 

[NateO]

Hello again 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...

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:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> URange()
<SPAN style="color:darkblue">With</SPAN> Sheets(1)
    .UsedRange
    <SPAN style="color:darkblue">With</SPAN> .UsedRange
        MsgBox .Item(.Rows.Count, .Columns.Count).Address
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

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:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> URange2()
<SPAN style="color:darkblue">Dim</SPAN> myRng <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">With</SPAN> Sheets(1)
    .UsedRange
    <SPAN style="color:darkblue">With</SPAN> .UsedRange
        <SPAN style="color:darkblue">Set</SPAN> myRng = .Item(.Rows.Count, .Columns.Count)
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Debug</SPAN>.<SPAN style="color:darkblue">Print</SPAN> myRng.Address(False, <SPAN style="color:darkblue">False</SPAN>)
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

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

 

[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)?

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

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.

 

[Ekim]

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

 

[Von Pookie]

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

 

[tusharm]

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

Option Explicit

Sub testIt()
    With ActiveSheet
    MsgBox .Cells(ActiveCell.Row, .Range("bounce").Column).End(xlToLeft).Address
        End With
    End Sub

or

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.

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