cm comments in-line

"bpascal123" wrote in message
...
Hi,

Thanks for your code.
I went through part of it not without difficulties. I should have told
first it's my first vba code from a personal task...and i'm not really
aware of vba objects that's why most code here is shorter than mine
and seems a lot more efficient. I'll try to read them further.
However, i find it easier to learn vba code while implementing a
personal task rather than from reading lines of code. Any further
advice for this feeling?

cmI saw several different solutions proposed, each using a slightly
different approach. My advice would be to invest the time to understand
exactly how and why each proposal works. Rather than "reading lines of
code", paste them into a code module and use a combination of the
debugger and <F1 (the built-in help) to discover that understanding. I
found that when I began using the debugger's Locals Window my level of
understanding of vba objects increased dramatically. [VBE Menu: View |
Locals Window ]

From what I can understand, I should use less the select method and
use more the offset property.

cm It's not so much using offset instead of select that gains
efficiency. As I understand it, you gain the most efficiency when you
write code that does not update the display- .Select and .Activate do
update the display. In code you can read or modify a range directly
without ever selecting it. I was going to suggest that you read the
entire range into an array, manipulate the array within vba, then write
the updated array back to the worksheet as a method to increase
efficiency (only "touch" the worksheet 6 or 7 times total, rather than
"touching" it for each cell as you iterate through the data) -- but
never did because that has already been posted. So: the efficiency gains
come from reducing manipulation of the display (and, I think, from
reducing the number of "touches" on the worksheet.)

It would quite change the design of the
code and programming habits (I have a 2-3 years programming experience
with non-object languages). Would learning C++ help to find vba
easier?

cm I cannot speak to that; I have no C experinece at all.

So, I have found a fix on the secondary loop that makes the code work,
see 'A
However, I don't know if it's as rock solid as what I can find from
average and experts codes. Whatsoever, i know it's not efficient. I
understand I should get some training with vba arrays and the job done
in vba array.

Are vba arrays treated in segment data or heap or stack memory. Are
variant and fixed size arrays treated the same?

cm I suggest that you read the help regarding arrays. It may help
answer your question. A variable of type variant can hold an array --
and that is different from an array variable delared with a Dim
ArrayName() statement. My suggestion was going to use three arrays -
one fixed array each for your column 1 and column 2 data, and a variable
two dimensional array that "grew" (using ReDim) each iteration. What I
don't know is the efficiency cost of using redim to extend an array each
iteration (vs) using a fixed array. The trouble with using a fixed
array in this case is that you do not know in advance how many rows will
be required in the final result.

[ snip ]

cm A couple comments regarding your original code:

I noticed that in some places you used [ wks.cells(...) ] and in other
placed you left off the wks qualifier [ cells(...) ]. Dangerous
practice -- in fact, I noticed it when I (inadvertantly) created a test
environment where the default worksheet object (the one referenced by
Cells without the preceeding object qualifier) was different by the time
the [ Cells(...) ] was executed than when [Set wks = Activesheet ] was
executed which caused erroneous results.

Also, as a matter of personal preference, I much prefer using
debug.print than msgbox while testing code. [ View | Immediate Window ]
to see what debug.print has printed. In fact, I use a combination of
debug.print, setting breakpoints, single-stepping through code, Locals
Window and the screen-tip of variable contents when hovering over a
variable while execution is stopped during a breakpoint.

Welcome to learning VBA! You have come to an excellent place to ask
questions and receive good answers. Come back often just to lurk ---
you will learn much from the solutions and answers posted here.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)