Retaining a range definition

[PinkeyNBrain]

Problem: I want to move a range of data but using code similar to[CODE VBA]src_range.Cut Destination:=des_range[/CODE] I find src_range.Address is changed to match des_range.Address. In some cases my value for des_range is compromized. I want to retain the original definition of src_range.Address.
I tried[CODE VBA]set org_src_range = src_range
src_range.Cut Destination:=des_range[/CODE]but found that my address for org_src_range gets changed as well.
I'm not a VBA wiz by any means but did come up with this. It works although clunkey. Looking to see if anyone has an opinion on improving it. I know what I need to add should it be called with negative row/col shift vals. But for what there is now, what would work better? Thanks in advance.
[CODE VBA]Function shift_data_block(cell_ref As Object, _
Optional row_shift As Integer = 0, _
Optional col_shift As Integer = 0) As Boolean
Dim top_left_cell As Object
Dim src_range As Object
Dim des_range As Object
Dim src_rows As Integer, src_cols As Integer

shift_data_block = False
On Error GoTo err_exit

Set src_range = cell_ref
Set des_range = src_range.Offset(row_shift, col_shift)
src_range.Copy Destination:=des_range

' Clear out horizontal component if any
If row_shift > 0 Then
Set top_left_cell = src_range.Range("A1")
src_cols = src_range.Columns.Count
Set des_range = Range(top_left_cell, _
top_left_cell.Offset(row_shift - 1, src_cols - 1))
des_range.Select
des_range.Clear
End If
' Clear out vertical component if any
If col_shift > 0 Then
Set top_left_cell = src_range.Range("A1")
src_rows = src_range.Rows.Count
Set des_range = Range(top_left_cell, _
top_left_cell.Offset(src_rows - 1, col_shift - 1))
des_range.Select
des_range.Clear
End If

shift_data_block = True

Exit Function

err_exit:
Err.Raise Err.Number, Err.Source, Err.Description

End Function[/CODE]

 

[SkipVought]

Hi,

So prior to the Cut, store the Range.Address. Then MOVE the data. You still have the reference (address) where the data originally resided.

 

[PinkeyNBrain]

Indirectly tried the saved address idea. Consider

Select Case some_val
   Case 1: Set cell_ref = default_range1   ' Assume this is selected
   Case 2: Set cell_ref = default_range2
End Select
save_addr = cell_ref.Address
cell_ref.Cut Destination:=cell_ref.offset(1,1)
set cell_ref = Range(save_addr)
Debug.Print cell_ref.Address          ' This will be what I expect
Debug.Print default_range1.Address    ' This has changed - not what I want

I've tried using passing the varialbe to a Sub and usign ByVal as well. No difference. The outlying issue for me is that more than just the variable I'm directly working with is getting changed. You commented using MOVE. I can find reference to that at the sheet level but am missing how to use it at the cell level.

 

[SkipVought]

When you CUT n paste you, in effect, MOVE the data.

I guess I don't have a CLEAR picture of your problem.

Maybe you should rather explain WHAT it is you're trying to accomplish rather than HOW you think it ought to happen. No cut, paste, range etc. in this explanation, please.

 

[PinkeyNBrain]

MOVE = CUT – Got it. Your usage of caps caused me to think you were referring to another command. Usually I see people asking for code more over than concepts - but here goes:
- User enters data into a textbox on a worksheet (not a cell but an activeX object). Hits enter.

- Sheet level subroutine textbox_KeyUp looks for vbCR and processes data user typed into textbox

- Data from the textbox is parsed into a number of sub-tables on the sheet.

- When certain tables get 'full', I shift them to another area to make room for more data in the main screen. Just where it is moved to is determined in context - hence the reference to the 'Select Case some_val' statement in the code example above.

- So in essence what I am trying to accomplish is to use VBA to move ranges of cells around the screen (e.x. src_range.Cut Destination:=des_range). I am trying to do this in such a way that the variables I use to set src_range (ex. default_range1) do not get changed. My last snippet of code example was to demonstrate that I can use the .Address function to restore the cell_ref variable, the default_range1 variable is still being changed during the cut/paste process even though I'm not working with that variable on that line of code. I suspect default_range1 is getting changed due to some inheratence property.

- I have developed such a way to move data without altering my original range definition (ie. shift_data_block) but in an effort to expand what I know, I’m looking in here to see if someone has a better approach.

 

[SkipVought]

*sigh* you couldn't refrain from using Excelese to explain what you are doing.

When certain tables get "full"...

More than how many rows? If "full" is a limitation in Excel, then you ought to be using a proper database that has larger limits, like Access or Oracle.

But really, this is not how databases work. I suspect that your "full" tables are a relative handful of rows.

You're making this much more complex than it need to be, it seems to me. Although Ive rarely used the Data > List feature, which displays a list/table navigator for data entry, it's there as a feature in Excel. You should NEVER have to deal with a bitty table that you then move into the actual table.

So when you're adding data in the bitty table, how do you know that you are not entering data that is a duplicate of data in the actual table? I don't understand why the data cannot be entered directly in the actual table? There are some unstated requirements in the WHAT.

 

[PinkeyNBrain]

Skip - Thank you for your time. My attempt at a colloquial explanation of what I’m doing, versus demonstrating thru code, has fostered a completely wrong picture of what I’m doing. In this case a 'table' for me is roughly a grid 3x10 in size. The entire screen has columns A-0 (15 columns) and rows 1 thru 30. This gives me room for 15 such grids on the screen. When one of these grids is full of data, I move it to another location. However I am suspecting that further attempts to seek assistance with writing more elegant code will result in a discussion of whether the application I’m using is indeed the right one versus some other application altogether. So I will once again express a sincere thank you for taking your time to assist me. Thank you.

 

[SkipVought]

It's like you're telling a carpenter the problems you're having driving and removing screws with a dinner knife. Can it be done? Sure, I've even done so. But that's not the proper tool of choice.

I'd make another suggestion. Make each of your biddy tables a Structured table. Structured Tables have range reference, TableName[#Data]. Copy/paste the data, then delete the data. The table template remains!