Most Effective loop code to sort/lookup

[krdale]

Hi Guys,

Iam starting of on a new project in my VBA career, instead of just hitting it and building a "small book" of subs and functions, I want to start of by asking some general questions so that I end up with a code as clean as possible. I am basically looking for the quickest way to list values from parts of rows based on certain cell values. My problem is that the location of these cell values (and their headers) change from time to time.

The picture below shows a draft on the layout, the green areas will change and has to be dynamic in size while the light blue and gray areas will be fixed in width. However, if I add a Theme (green area) the blue/green area will shift one column to the right. I guess I can solve that with named ranges or with the help of "FIND" vba code perhaps?

The sheet is used for trading positions, and the Grey area will contain the positions, ive put in 3 random ones for the example (Cell AH4-8).

The functionality I need is to be able to sort/show in different ways the positions(gray area) based on Sub Themes, Themes, Assets, Type(thematic, event, tactical) and direction(bull/bear) as I choose. The different "sortings" will be in separate sheets.

So i would for instance have a sheet where i just sorted all the positions based on theme, it would looke someting like this: Sheetname: THEMES

REFALTION
EURUSD xxx xxx xxx
SP500 xxx xxx xxx
Totals: xxx xxx xxx

DECOUPLING
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx

EUROPE
EURUSD xxx xxx xxx
Totals: xxx xxx xxx

ASIA
SP500 xxx xxx xxx
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx

Today we use a coding that is coded with absolute references that is I cannot change any elements of the sheets without having to change code for hours. And the code we use is utterly slow. (15 seconds to update sheets, on a Intel Xeon Hexa Core (6x2.8Ghz with 8GB DDR machine...)

I think the code is particular slow as the old sheet use only two column and "1 to 9" and "a to z" so that it will look through all rows looking for 1A, then 2A, 3A etc til its comes to 10Z before its done. I imagine how it is proposed here will be quicker?


Questions:

1. Structure
I guess this isnt to easy to answer, but how should I ideally cope with the structure? I imagine a structure where I have one sub that reads the info given 1-6 inputs. I.E:

Sub Sort(conSubTheme, conTheme)
Sub Sort(conAsset, conType, conDirection, conPosition)
etc?

Is possible as here to have a variable number of inputs to a sub? Or will it be overly complicated? Any smart ideas?

2. "Active Themes"
How can I easiest identify the Active Themes and sub themes? And give them a number? I assume I should have a number to facilitate the loop to next theme. So that for instance conSubTheme1 is column A, etc, and conSubTheme9 is column I while conTheme1 is column J, and conTheme2 is column K and conTheme5 is column N.

The code will have to read row "xx"4 to A4, and ideally "group" these into Themes and Sub themes. I can to facilitate this either use "T" or "S" for instance in the cell above a theme name. I.e Cell a3 = S and a4=Long Dollar so i know Long Dollar is a Sub Theme and not a Theme or Asset for that sake?
I would prefer to do this without the "S/T/A" etc.

3. Active cell or "ranges/offset"
What's quickest in terms of code? Looping using ActiveCell(0,+1) etc or Range("DATA").Offset(0,+1) ? Am I imagining this or is the Range.offset approach quicker? (i have screenupdating set to false)

4. Hierarchical listing
What would be the best way to sort on Themes (column J4-Q4) then bull/bear (column AD/AE) for instance? When I say sort I mean a code that will group the bull and bear positions under each Theme. (and if there is no positions in that theme do not list the theme.

Historically I did this with a "conditional write sub", which i gave conditions in the name. that is to sort Theme 1 bull and then bear i would do
sub WriteData(Theme1,bull)
sub WriteData(Theme1,bear)

Or perhaps something like this:

For i 0 to 5 do

sub write data("Theme" & i ,bull)
sub write data("Theme" & i ,bear)


I would greatly appreciate some advice, and I will repost with code as soon as i have started on the new code as i understand it might be easier to give advice that way.

 

[cornflakegirl]

I would use Advanced Filter to do the finding and moving of data. Then you just need code to set up the sheets, identify which criteria belong on which sheet, and do things like put in your headers, totals, and blank spaces.

If you stuck titles in row 3 for every time you wanted a new sheet created, that would probably be enough information to be able to create the code.

 

[krdale]

I would use Advanced Filter to do the finding and moving of data. Then you just need code to set up the sheets, identify which criteria belong on which sheet, and do things like put in your headers, totals, and blank spaces.

If you stuck titles in row 3 for every time you wanted a new sheet created, that would probably be enough information to be able to create the code.

Thanks for your reply, I have not used Advanced Filter before, but will that be able to report the same line under several themes in the same "table"? Or does it only use a "row" once?

I only need around 4-5 sheets with several sub groups (and sometimes positions will be duplicated in those).

 

[cornflakegirl]

Advanced filter would be able to give you eg the Reflation table. You would then need to reset the criteria to generate the Decoupling table. However, that would be quite straightforward.

 

[krdale]

Advanced filter would be able to give you eg the Reflation table. You would then need to reset the criteria to generate the Decoupling table. However, that would be quite straightforward.

okei, but I have to do this in VBA code I suppose? We do quite alot of calculations on these positions thats why we did it with loops initially. But might be that the filter does the job better!

 

[cornflakegirl]

Yeah, you'd use VBA to create the sheets etc.

In a dummy file, if you have Sheet1 set up per your initial post, then on Sheet2, in A1 put REFLATION and in A2 put X. In A4 to E4 put your headings from AH4:AL4 on Sheet1. Select A1:A2 (because Advanced Filter needs something selected), then go to Data - Filter - Advanced Filter. OK the message about headers. Select Copy to another location, set the List range as Sheet1!$A$4:$AL$7, the Criteria range as $A$1:$A$2, Copy to as $A$4:$E$4. You'll see that the records come through. You can use macro recorder to generate the code for this.

So you'd need to loop through your various page headings and table headings (in Sheet1 A3:AG4), and you'll need code to determine the placement of the tables on the sheets you create etc. But using Autofilter should make the code shorter to write and faster to run.