How can I determine what the "Range" for a cell is?

[leonepaolo]

Hi!

I know we can do this:
dim x, y, z as Integer
x = 25
y = 5
z = Cells(x,y)

How can I figure out that in this instance x = AA ?

What I'm ultimately trying to do is move a range of cells. The information is being updated daily and the existing columns need to be moved over 1 row to the right as I want to see the most recent data first.

If I don't need to approach the solution this way can you please point me in the right direction.

Thanks in advance.
Paolo

 

[Gavona]

I assume you mean 1 column to the right, not row.
x is the row number, y is the column number
z is a range not integer

Cells(x,y).column will tell you the column

This will find a cell and insert a new column to the left of it

Cells.Find(What:="xx", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireColumn.Insert

This will insert some cells, shifting the existing cells one column to the right.

Sub test()
With Worksheets(1)
.Range(.Cells(7, 5), .Cells(12, 5)).Insert Shift:=xlToRight
End With
End Sub

Hope this helps you.


Gavin

 

[leonepaolo]

Hi Gavin,

Thanks for the help.
I've tried your second solutions (as I'm not sure about affecting the spreadsheet by inserting an entire column).

I have tried your second solution. What it does is move everything to the right of the right most column versus moving every column one column to the right. i.e. Let's pretend I have data in columns A, B & C. The result was A went to D, B to E, and C to F.

What I can do is create a loop that moves the data (per column) one column at a time. Is there a way for me to avoid the loop?

Thanks,
Paolo.

 

[Gavona]

Excel is very good. It is most unlikely to mess formulae up if you insert a whole column.
The second approach - moving just a few cells to the right is more likely to put data in the wrong place. mess up any formulae involving ranges (like Sum(G1:G99)for example that you might have.

Fill a1:h14 with text ~(anything).
Run my second example exactly as posted.
For me it inserts blank cells in E7:E12 shifting what was in those cells, and everything to the right of them, one column to the right. Is that what it does for you? I don't see how A would go to D etc.

(You don't need to mess with code to view this behaviour. Highlight a block of cells. Right click, Insert, Shift Cells to Right)

Try changing Cells(12, 5) to Cells(12, 7) and running the code again.

Back to your challenge: I think we need to understand what you are trying to do rather better. Is this right?
Today is 27Nov
1. 27 Nov data is in column C
2. 26Nov data is in column D
3. 25Nov data is in column E etc
4. Tomorrow you want 27Nov data to be in col D, 26Nov data to be in col E etc.

Assuming I have that right:
5. Is there anything else in the columns, anywhere on the spreadsheet, that should not be moved with the data?
6. Do any formulae refer to data in any of these columns? What do you want to happen to these?




Gavin

 

[leonepaolo]

Hi Gavin

I'd like to set the tone for this e-mail with "WOW".

Your second example does work perfectly when moving one column at a time. I encountered my problem when trying to move more than one column. I found the same "jump" when trying the right click and insert method.

When filling ranges a1:h14, and using your code exactly it worked exactly as you described. It even continued to work perfectly when I modified it to look like:
"Range(Cells(7, 5), Cells(12, 5)).Insert Shift:=xlToRight"

Changing Cells(12, 5) to Cells(12, 7), well now, that certainly proved interesting. From this it became glaringly clear what the code really does. It moved EVERYTHING to the right of Cells(12, 5), to the right of Cells(12,7).

So, Gavin (Code Guru) here's what I tried next. I asked it to shift column A only to the right i.e. Cells(1, 1) to Cells(14, 1). And guess what,everything to right of it shifted one cell to the right. This is fabulous because I figured I'd have to determine the full range at run time and now I don't.

You, kind Sir, have made this much easier than I ever possibly expected. Thank you! Thank you! Thank you!

You're also right on in your assumption of how my data is being recorded:
Today is 27Nov
1. 27 Nov data is in column C
2. 26Nov data is in column D
3. 25Nov data is in column E etc
4. Tomorrow you want 27Nov data to be in col D, 26Nov data to be in col E etc.

Well, well. Aren't I glad you asked quetions 5 and 6.
It seems as though when I shift the cells to the right, my formulas follow the numbers - I need them to stick with the predetermined cell coordinates.

I tried coding a solution to my problem and it works fine. After I move the cells, I have vb modify the cell to contain the formula I want.

What I don't know yet is how long this will take. Believe it or not but I'll have to modify about 80 thousand cell calculations this way. I realize it sounds ridiculous but its true. I also realize how out of sorts this may sound but then I'll be creating a search to find specific results. So it's not as though I expect anyone to believe I'll be reviewing it manually.

Is there a way for me to eliminate the step of recoding cell formulas?

Thanks Gavin,
Paolo

 

[Rofeu]

Hi,

why not just copying your data one Column down the line?

 Sub MoveToNextColumn()

With ActiveSheet.UsedRange
    .Copy Destination:=.Offset(, 1)
    .Columns.ClearContents
End With

End Sub

Cheers,

Roel

 

[Rofeu]

Hold on. Forgot something:

.Columns(1).Clearcontents



Cheers,

Roel

 

[Gavona]

I think Rofeu's suggestion is the same as simply inserting a column. However you could modify it so that data in the inserted column was deleted but formulae were retained.

.columns(1).SpecialCells(xlCellTypeConstants, 23).ClearContents

You still haven't really given us enough info about the structure of your spreadsheet. If all the formulae are in the same column as the data they refer to then inserting a column is the way to go. If that won't work for you then explain why.
You certainly don't want to modify all those formulae - even using code.



Gavin

 

[SkipVought]

Paolo,

Often, when there is a question, there's ALSO the real question or issue that is never asked, but implied in the question.

When I see users doing on-going copy 'n' paste, there is often a much better solution.

When I see users accumulating data in a REPORT format (non-normalized data), rather than a TABLE format (normalized data), there is usually a better solution.

So the question I would ask is, where are you getting this daily data that you are moving columns to the right, etc??? Could this data acquisition be accomplished in a much better way (query, import???)

If your data were in a table with an additional column for DATE, (the dates that are in your coumn headings), chances are that you would NOT need to do all this inserting and moving, IF your data were appended to the table. The REPORT would probably be a simple PivotTable refresh, as long as your SOURCE DATA table were defined DYNAMICALLY to your PivotTable.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[leonepaolo]

Hi Gavin,

After reviewing this note several times I think I should start off with: Consider this a data base i.e. rows are records and columns are record values (on a specific date).

I'm not really sure what I'm going to want the spreadsheet to like in the end because I still don't know what the results of my calculations will be. And therefor I don't know how many columns I'll need for calculating. I pretty much figured I'd move things over to the right (and ajust my constants for column and row referneces) as I go.

I'm also not sure what I'll want to do with the bottom - I doubt anything as the records really don't relate to each other this way. It's through the Calculations that I'll want to compare the data.

Here's how I see it for now:
I don't start with data until:
row = 10
col (x) = 11

col (1) contains the Index or ID
col (2-10) will contain formulae refering to same row data from col 11 and on.

I've tried to layout a sample spreadsheet below.

f = formulae
dt = data
... = unshown columns - for this example as they're redundant

1 | 2 | 3 | ... | 11 | 12 | 13 | 14 | ...
2 | | | ... | | | | | ...
3 | | | ... | | | | | ...
. | | | ... | | | | | ...
. | | | ... | | | | | ...
. | | | ... | | | | | ...
-----------------------------------------------------
10 | f | f | ... | dt | dt | dt | dt | ...
11 |
12 |
13 |
14 |
15 |

In the end, I believe I'll create a seperate ss (spreadsheet) for the calculations

But as far as I can tell, when I insert a new column on the data ss whether or not the formulaes are on that ss or another, the cell references in the formulaes follow the movement of the data. I want to keep the references with the original cells.

I don't know how else I can describe it.

Thanks for your continued support,
Paolo

 

[Gavona]

Can you give an example or two of the formulae?

Do the formulae only operate on a single record or do they reference several records?

How many columns of formulae?

How many rows(records) and columns(days) of data will you have.

Skip, as always is right. The best solution may involve re-thinking the approach. I am sure that the more you tell us of your challenge the better the solution we will help you to find.


Gavin

 

[leonepaolo]

Dear Gavin and Skip,

It's nice to see you folks work together here.

You guys are really intiutive, it's shocking.

First of all skip, there is no date that comes with the data.

The data is the same data everyday, it's just that the data is reranked. So, essentially it's like this:
Days are across the top, there is no pattern pattern persay, its just that trends tend to continue. My responsibility is to try and "catch" the records as they make their moves up the top of the list. The data is provided to me like this:

... 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1
... A | A | A | B | B | C | A | C | H
... B | B | C | A | A | B | B | H | A
... C | C | B | C | F | F | C | A | C
... D | E | E | F | E | A | H | I | G
... E | D | F | G | G | H | I | G | I

Now, here's what I'm doing. I'm tracking the index value of each symbol for each day (the use of question marks is just to try and keep the columns together), so I'm changing it to this:

... | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1
A ... | 1 | 1 | 1 | 2 | 2 | 4 | 1 | 3 | 2
B ... | 2 | 2 | 3 | 1 | 1 | 2 | 2 | ? | ?
C ... | 3 | 3 | 2 | ? | 1 | 3 | 2 | 1 | 3
D ... | 4 | 5 | ? | ? | ? | ? | ? | ? | ?
E ... | 5 | 4 | 4 | ? | 4 | ? | ? | ? | ?
F ... | 6 | ? | 5 | 4 | 3 | 3 | ? | ? | ?
G ... | 7 | ? | ? | 5 | 5 | ? | ? | 5 | 4
I ... | 8 | ? | ? | ? | ? | ? | 5 | 4 | 5
.
.
.

As I've mentioned there are about 8000 symbools. Each symbol can only exist once on any given day. My responsibility is to pioneer the tracking of the movements up the list (once I've figured it out, there will be times that I'll be looking for downward momentum).

My biggest problem, which really stood out once Gavin first started to help me is what do I want it to look like in the end. Well, Gavin, I have to admit that I'm making it up as I go. My first problem was incorporating the data into the SS. The thing is I don't know what meaningful changes are. Is it a moving average from one day to the next, or from one 5 day period to the next. I don't know, I have to determine the success in predicting future movement based on the results of calculations.

I have to admit that I'm really cheating here. This isn't something I (or anyone else that I know of) used to track manually and I've had the epiphany of coding it to save time. Frankly if I hadn't tinkered with code before, I would've never even embarked on this in the first place. People used to use calculators, I couldn't imagine - I do own one but I'm sure you get what I mean.

And you Skip, have really made me realize that I have no clue as to the full potential of the Excel resource. I've been reading through post and have heard of pivod tables but I have yet to look into them. My first job is incorporating the data. I've basically figured out how to do that now.

A further note to Skip is, maybe I don't have enough respect for the work vb programs do. I have to admit that relying on them to perform my redundant tasks has kept me from seeking inherent or more stable talents that are part of the software.

I sincerely want to thank you both for posing questions that help me think and better understand what I'm trying to accomplish. I am trying to learn how to fish .

For now, my last question is, when I move the data, how do I keep the formulas referencing the original cells as opposed to following the data.

And please, if you have any further comments or questions that I should be asking my "self", please forward them.

Thanks,
Paolo

 

[Gavona]

Are you expecting that there could be a relationship between the way one of your symbols moves and the way another moves? Or are you looking for trends for individual symbols?

What is the maximum number of days of data that you will be analysing?

Does each symbol appear in each day's list without fail? (I am thinking that it could be more efficient to sort the data than to use formulae).

Please do answer the above questions! However, here is a solution that addresses your question.

Using formulae however:
Todays data is in Column A, starting in row2
Column C contains a list of your symbols so A2=A, A2=B etc
Cell B1 contains the formula =today()
Cell B2 contains the formula
=MATCH(C2,A$2:A$8001,0)
this is copied down

(this bit you could automate with a macro)
Insert a new column D
Select column B
Copy
Select Column D (the blank column you just inserted)
Edit, PasteSpecial, Values

Next day bring your new data into the worksheet, ColumnA, starting in row2 (consider if you need to clear A2:A8001 first)

Insert a new column D
Select column B
Copy
Select Column D (the blank column you just inserted)
Edit, PasteSpecial, Values

and so on.

That doesn't sort out your trend analysis. It could probably be adapted to organise the data better in order to facilitate that analysis but I think it addresses your original questions.



Gavin

 

[leonepaolo]

Gavin, your questions are fantastic and a pleasure to answer. You've given me many additional opportunities to ponder further and that's half the fun.

Right now, I'm looking at the movement of one Symbol. I figured its change, day over day (recent), weekly average over weekly average, montly average over mo'ly ave and maybe periods of all three. I may consider broader measures later.

I figured 3 months or ~66 days would do it. I downloaded more for backtesting purposes, I'm at a hundred and five right now. I'll probably just let the data fall off the right side of the spreadsheet - unless there are good reasons to delete sooner i.e. system memory, performance...

Each symbol does appear without fail.

Sorting data in a spreadsheet, wow, you can do that?
Here's the basic scoop:
1. Them main ss is for referece and tracking purposes - it's sorted alphabetically.
2. Each days data is sorted with the most valuable being at the top and the least being at the bottom
3. In main table (point 1 above), I'm tracking each symbols position for the dates being analyzed.

In affect, what I do is:

4. when I import the symbols from another ss, they are sorted their own way, so I assign that data, in that order, to an array.
5. I cycle through each symbol on the main ss, and through each element of the array in search of the match.
6. I then assign the index value of the element to the first, leftmost column of the ss - which is why I needed the space.

Thanks for the example you provided. I scanned it and it looks interesting. I'll be trying it first thing in the morning.

Paolo

 

[Gavona]

Too much data to normalise it all in the way Skip suggested - though he may well come up with a better/more creative way.
----------------------------
Sort Method:
Data in columnB A,c,d,g,....
Rank in columnA 1,2,3,4...
Columns to use row 1 for the field heading

Highlight (select) ColsA and B (I assume you know how to do that)
Data,Sort..by Symbol
Just the cells selected are sorted - by symbol

then copy the Rank values
Click on the column letter above the previous days data to select that column
Right click
Insert Copied Cells
Type the date into row1 of the copied data

------------------------
I am not sure how I would create averages etc. Probably inserting columns and creating the values using VBA BUT the real issue is that you don't know how you are going to use or view the information - so the best way to structure it is not clear. An approach:

Having structured the data as we have been discussing you could copy the Date row and the row containing a single symbol's rank for each date and PasteSpecial,transpose paste them. This converts rows to columns and gets us towards normalised data as Skip suggested.
I think that Excel would work much better for you in this way.

Then chart (graph) the data, looking at the single Symbol at a time.
Excel charts have facilities to find lines of best fit and allsorts but in any case just looking at the data might help you see patterns.
(If you think two symbols might be related then chart them both together as separate lines on the same chart)

(Probably better to post a new thread about identifying trends in data once you have worked through how to handle it.)


Gavin