Process dumped into an array...and then back to sheet

[Adrien Huvier]

Le 27/03/2013 10:03, Claus Busch a écrit :

still a little bit quicker:

Sub Tester3()
Dim LRow As Long
Dim st As Double

st = Timer
LRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("L1").Formula = "=find(right(E1,13),E1)=8"
Range("L1").AutoFill Range("L1:L" & LRow)
MsgBox Format(Timer - st, "0.000")
End Sub


Regards
Claus Busch

Hi,

I think you can write the Formula at once and be even faster:

Sub Tester4()
Dim LRow As Long
Dim st As Double

st = Timer
LRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("L1:L" & LRow).Formula = "=find(right(E1,13),E1)=8"
MsgBox Format(Timer - st, "0.000")
End Sub

 

[GS]

Thanks, Ron! I was not aware of the limitation and so recoding to
eliminate Transpose is no problem...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Actually, XL2007 max rows is about 1 million AFAIK, but is memory

I'm a happy camper with this.

Thanks Garry

Ron raised a very good point and so here's my revised version...

Sub Tester2()
Dim vDataIn, v1, v2, vDataOut(), n&
vDataIn = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
ReDim vDataOut(1 To UBound(vDataIn), 1 To 1)
For n = LBound(vDataIn) To UBound(vDataIn)
v1 = Split(vDataIn(n, 1), ","): v2 = Split(v1(3), ":")
vDataOut(n, 1) = (v1(2) = v2(2))
Next 'n
Range("E1").Offset(0, 7).Resize(UBound(vDataOut)) = vDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Howard]

Ron raised a very good point and so here's my revised version...



Sub Tester2()

Dim vDataIn, v1, v2, vDataOut(), n&

vDataIn = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)

ReDim vDataOut(1 To UBound(vDataIn), 1 To 1)

For n = LBound(vDataIn) To UBound(vDataIn)

v1 = Split(vDataIn(n, 1), ","): v2 = Split(v1(3), ":")

vDataOut(n, 1) = (v1(2) = v2(2))

Next 'n

Range("E1").Offset(0, 7).Resize(UBound(vDataOut)) = vDataOut

End Sub



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Garry,

2.547 seconds to do 500,000 rows!!!

Claus offered up a code that ran in 1.045 seconds for 500,000 rows.

And I have not yet tried Adrien's yet.

It's all pretty amazing to me.

I'm under no illusion that I will ever be able to write code like this, it is daunting to even understand how it works as I look at it.

I really appreciate everyones contributitions.

Thanks to all of you.

Regards,
Howard

 

[GS]

Garry,

2.547 seconds to do 500,000 rows!!!

Claus offered up a code that ran in 1.045 seconds for 500,000 rows.

And I have not yet tried Adrien's yet.

It's all pretty amazing to me.

I'm under no illusion that I will ever be able to write code like
this, it is daunting to even understand how it works as I look at it.

I really appreciate everyones contributitions.

Thanks to all of you.

Regards,
Howard

Howard,

Claus's suggestion is definitely going to be faster than working an
array. Adrien's suggestion makes Claus's a bit more efficient again!
However.., I'd take it one step further so as to reduce the amount of
recalcs when a sheet with that many rows is activated (or when the file
gets opened)...

Sub Tester5()
Dim lRowCount&, dTimer#

dTimer = Timer: lRowCount = Cells(Rows.Count, "E").End(xlUp).Row
With Range("L1").Resize(lRowCount)
.Formula = "=find(right(E1,13),E1)=8": .Value = .Value
End With
MsgBox Format(Timer - dTimer, "0.000")
End Sub

While both of these do the task without doubt, much faster, they do not
address your request regarding dumping data into an array, working the
data, then dump the results back into a sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Howard]

Howard,



Claus's suggestion is definitely going to be faster than working an

array. Adrien's suggestion makes Claus's a bit more efficient again!

However.., I'd take it one step further so as to reduce the amount of

recalcs when a sheet with that many rows is activated (or when the file

gets opened)...



Sub Tester5()

Dim lRowCount&, dTimer#



dTimer = Timer: lRowCount = Cells(Rows.Count, "E").End(xlUp).Row

With Range("L1").Resize(lRowCount)

.Formula = "=find(right(E1,13),E1)=8": .Value = .Value

End With

MsgBox Format(Timer - dTimer, "0.000")

End Sub



While both of these do the task without doubt, much faster, they do not

address your request regarding dumping data into an array, working the

data, then dump the results back into a sheet.

This latest code you posted really gets after it...
1.324 sec withe the .value = value
0.781 sec with .value = value commented out.

After my inital shock at ALL the speedy code, I have absorbed a bit of new (to me) info.

How simple can this be once I have time to digest it:

"=find(right(E1,13),E1)=8"

Takes the 13 digits on the right and looks for it within itself and you have a true or false. Seemed like magic at first but quite logical now. I guess the geneiuse in knowing that's what to do.

I guess the only question I have now is, is there any hidden advantage to having text true/false in column L or a formula in column L that produces a true/false? Nothing pressing about the question beyond curious.

Kinda like when I asked about doing the dump to an array and then back again, I just didn't know any different and remembered you advising someone on it and I sure didn't know how to do it.

So, then over the last several minutes I was pondering how to use this bullet fast code to highlight the false producing cells as opposed to looking in column L... lo and behold I see Clause offering up some code in the post just below this one about cond. formatting. I took that code and modified it using the "=find(right(E1,13),E1)=8" formula and did this:

..FormatConditions.Add Type:=xlExpression, Formula1:="=find(right(E1,13),E1)=8=FALSE"

Zips through 500,000 rows in 0.109 sec.

So now I have best of both worlds and all thanks to the likes of you and others.

I have to chuckle and think...Do you guys EVER get stumped?

Thanks, Garry.

Howard

 

[GS]

This latest code you posted really gets after it...

1.324 sec withe the .value = value
0.781 sec with .value = value commented out.

Seems worth the 1/2 second to convert to values, IMO!

After my inital shock at ALL the speedy code, I have absorbed a bit
of new (to me) info.

How simple can this be once I have time to digest it:

"=find(right(E1,13),E1)=8"

Takes the 13 digits on the right and looks for it within itself and
you have a true or false. Seemed like magic at first but quite
logical now. I guess the geneiuse in knowing that's what to do.

Actually, it looks for the 13 digits on the right at position 8 in the
string. This will work so long as the structure of the data doesn't
change such that the left side 13 digits no longer start at position 8!

My code will accomodate slight changes as long as the 13 digits follow
the 2nd apostrophe. Otherwise we'd have to test the elements for
matching length and/or IsNumeric.

I guess the only question I have now is, is there any hidden
advantage to having text true/false in column L or a formula in
column L that produces a true/false? Nothing pressing about the
question beyond curious.

As I mentioned, 500,000 rows of data is a lot of recalcs that aren't
really needed since the data in E isn't going to change. If the data
was being updated then the formulas would also auto-update for the new
data.

Since we're only talking a 1/2 second diff in processing time, I'd
prefer to not keep the formulas.

Kinda like when I asked about doing the dump to an array and then
back again, I just didn't know any different and remembered you
advising someone on it and I sure didn't know how to do it.

So, then over the last several minutes I was pondering how to use
this bullet fast code to highlight the false producing cells as
opposed to looking in column L... lo and behold I see Clause offering
up some code in the post just below this one about cond. formatting.
I took that code and modified it using the "=find(right(E1,13),E1)=8"
formula and did this:

.FormatConditions.Add Type:=xlExpression,
Formula1:="=find(right(E1,13),E1)=8=FALSE"

Zips through 500,000 rows in 0.109 sec.

Well.., congrats on figuring that out on your own! (Shows our time is
not wasted) But you could use the following simplified formula...

.FormatConditions.Add Type:=xlExpression, Formula1:= "=$L1=FALSE"

...since it's not necessary to repeat the amount of processing required
to return the value that's already there.

So now I have best of both worlds and all thanks to the likes of you
and others.

I have to chuckle and think...Do you guys EVER get stumped?

Yeah, I do occasionally. What throws me for a loop sometimes is when
I've been programming a lot in my 3rd party spreadsheet control
(fpSpread.ocx) that I use as an Excel replacement in VB6 apps. The
properties/methods differ in many ways and I find myself making
mistakes in Excel programming because I'm (subcontiously) writing
fpSpread code!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion