Split & Rearrange number

[Ron Rosenfeld]

At the risk of sounding pedantic, I think your first statement in the For
Each loop should more properly be this...

str = c.Value

But instead of doing this intermediate step of using a String variable, as
above, to feed to the Execute property of your 're' object, you could have
eliminated using this variable and done so directly (the way you did with
the c.Text value originally) like this...

Set mc = re.Execute(c.Value)

...that would have worked too, correct? Or will Execute only accept a typed
String entity in order for it to work?

Rick

Actually,

set mc = re.execute(c)

would also work just fine.

The "str" construct was left over from my testing to trying to figure out
exactly where the limitation was, and I didn't clean things up afterwards.

So far as c vs c.value, I believe they are equivalent, as I believe the value
property is the default for the range property, although I would generally use
c.value for clarity. As with the str construct, it was a leftover from my
testing to figure out where the hangup was.

I believe, although I have not found it documented (yet), that the TEXT
property of the range function returns what is actually displayed in a cell,
INCLUDING the limitation of 1024 characters that can be displayed. (I had not
appreciated that 1024 limitation up until now).

I had been in the habit of using the text property for regex stuff so as to
preserve the formatting of numbers, which would be important in designing a
regex. Obviously, for potentially long strings, this is inappropriate.

By the way, in your routine, you could eliminate your IF line. (I know you
like more compact routines )

If C Mod Columns.Count = 0 And C > 0 Then R = R + 1
Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C)

to:

Range("A1").Offset(Int(C / Columns.Count), _
C Mod Columns.Count).Value = Values(C)


Best,
--ron

 

[Rick Rothstein \(MVP - VB\)]

See inline comments...

Actually,

set mc = re.execute(c)

would also work just fine.

So far as c vs c.value, I believe they are equivalent, as I believe the
value
property is the default for the range property, although I would generally
use
c.value for clarity

I just noticed I forgot to include why I posted my pedantic statement.. I do
not like relying on default values without physically specifying them. As
you state, clarity is the reason. When having to look at old code for
maintenance or modification reasons, it is very easy to miss the fact that
'c' (or even a better, longer named object name) is not a variable, but
rather an object reference with the default property assumed. I've tripped
myself up over that one in the past when I thought taking this shortcut
approach didn't matter. After wasting a considerable amount of time (in a
code editing session some time back) tracking down a bug that would have
been a lot more obvious if I had used the property name with the object, I'm

I believe, although I have not found it documented (yet), that the TEXT
property of the range function returns what is actually displayed in a
cell,
INCLUDING the limitation of 1024 characters that can be displayed. (I had
not
appreciated that 1024 limitation up until now).

This is good to know and be aware of. Thanks for noting it. As for it being
documented, does a quote from Chip Pearson's website count?

 

[Rick Rothstein \(MVP - VB\)]

** I hate the Ctrl-Enter keystroke which Sends the document in OE **

See inline comments...

Actually,

set mc = re.execute(c)

would also work just fine.

So far as c vs c.value, I believe they are equivalent, as I believe the
value property is the default for the range property, although I
would generally use c.value for clarity

I just noticed I forgot to include why I posted my pedantic statement.. I do
not like relying on default values without physically specifying them. As
you state, clarity is the reason. When having to look at old code for
maintenance or modification reasons, it is very easy to miss the fact that
'c' (or even a better, longer named object name) is not a variable, but
rather an object reference with the default property assumed. I've tripped
myself up over that one in the past when I thought taking this shortcut
approach didn't matter. After wasting a considerable amount of time (in a
code editing session some time back) tracking down a bug that would have
been a lot more obvious if I had used the property name with the object, I'm
now a strong (to the point of being obnoxious sometimes<g>) advocate of
never using default properties without physically specifying them.

I believe, although I have not found it documented (yet), that the TEXT
property of the range function returns what is actually displayed in a
cell, INCLUDING the limitation of 1024 characters that can be
displayed. (I had not appreciated that 1024 limitation up until now).

This is good to know and be aware of. Thanks for noting it. As for it being
documented, does a quote from Chip Pearson's website count? See the last
line here...

http://www.cpearson.com/excel/values.htm

I had been in the habit of using the text property for regex stuff so as
to preserve the formatting of numbers, which would be important in
designing a regex. Obviously, for potentially long strings, this is
inappropriate. By the way, in your routine, you could eliminate your
IF line. (I know you like more compact routines )

If C Mod Columns.Count = 0 And C > 0 Then R = R + 1
Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C)

to:

Range("A1").Offset(Int(C / Columns.Count), _
C Mod Columns.Count).Value = Values(C)

Yeah, I know... now! I use that trick all the time and can't think of a
reason why I failed to see its use here. When I saw that you used it in your
code, and then realized I had forgotten to do it that way, I figuratively
kicked myself for having missed using it.<g>


Rick

 

[Max]

Ron,

.. Please try this routine instead, on your data, and let me know how it
works.

Tested here -- your revised sub runs fine ! cheers.

---

 

[Ron Rosenfeld]

Ron,
Tested here -- your revised sub runs fine ! cheers.

Thanks for the feedback, Max.

Best wishes,
ron
--ron

 

[Ron Rosenfeld]

This is good to know and be aware of. Thanks for noting it. As for it being
documented, does a quote from Chip Pearson's website count? See the last
line here...

http://www.cpearson.com/excel/values.htm

Oh yes, I would accept that. I never thought to look there for this piece of
information.

On the rest, I believe we are in violent agreement. It is exceedingly rare for
me to not specify the property and rely on the default. But, I did do it when
trying to figure out why my routine was not working on longer strings.
Probably, I should stop doing it even then.
--ron

 

[Rick Rothstein \(MVP - VB\)]

I believe, although I have not found it documented (yet), that the TEXT

Oh yes, I would accept that. I never thought to look there for this piece
of
information.

On the rest, I believe we are in violent agreement.

LOL ... Violent agreement... I like that phrasing.

It is exceedingly rare for me to not specify the property and rely on
the default. But, I did do it when trying to figure out why my routine
was not working on longer strings.
Probably, I should stop doing it even then.

When coding (either compiled VB or Excel VBA), I always specifically add the
default property reference, even in throwaway code (force of habit after all
these years I guess)... most of the time, Intellisense will help me out.

Rick