Made two versions; both run very slow . Any spare nitro around

  • Thread starter Thread starter Jon Macmichael
  • Start date Start date
J

Jon Macmichael

I've been recording some macros to append together, but put the
following together by writting it (well in my case it's a bitsa from
all the good code on this site). However, it runs very slow.

Every 3 letter code in cells down column("K") needs to be checked to
see if the same is found down column("A"). As a check, I've just been
putting a "1" in the same row in column("N"). Latter columns("K:N")
can then be sorted on column("N").

Column("A") has 3 letter codes from row 3 to 1402 (but changes each
run).
Column("K") has 3 letter codes from row 3 to 1626 (also changes each
run).

Can either of the following be speeded up? (Not sure if I put the 'VBA
carriage returns' in correctly).

Sub fill()

Dim mylastRow As Integer
Dim i As Integer
Dim dupeSymb As String
Dim k As Integer
Dim dupes() As Integer
Dim m As Integer
'Dim tstSym As String

Dim y As Integer
Dim tvSymList() As String
Dim lastTvSym As Integer

Dim t As Integer
Dim w As Integer
Dim tvFind As Boolean

k = 0

' //////////this next block too slow:- does 161 rows in 15 secs
'For y = 2 To mylastRow '- k
' If Application.WorksheetFunction.IsNA(Application.WorksheetFunction
_
.VLookup(Range("N1").Offset(y, -3) _
.Value, Range("tvsymbols"), 1, False)) = True Then
' Range("N1").Offset(y, 0).Value = ""
' Else
' Range("N1").Offset(y, 0).Value = 1
' End If
'Next y



' //////////this next block too slow:- does 328 rows in 15 secs
lastTvSym = Cells(Rows.Count, "A").End(xlUp).Row
ReDim Preserve tvSymList(3 To lastTvSym)
For y = 3 To lastTvSym
tvSymList(y) = Range("A1").Offset(y - 1, 0).Value
Next y

For t = 2 To mylastRow '- k
tvFind = False
For w = 3 To lastTvSym
If tvSymList(w) = Range("N1").Offset(t, -3).Value Then
tvFind = True
Range("N1").Offset(t, 0).Value = 1
Exit For
ElseIf w = lastTvSym Then
Range("N1").Offset(t, 0).Value = ""
End If
Next w
Next t

End Sub

Thanks
Jon
 
Hi
try the following code
Sub fill()

Dim mylastRow As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
mylastRow = Cells(Rows.Count, "K").End(xlUp).Row
With ActiveSheet.Range("N1:N" & mylastRow)
..FormulaR1C1 = "=IF(COUNTIF(tvsymbols,R[0]C11)>0,1,"""")"
Application.Calculation = xlCalculationAutomatic
..Value = .Value
End With
Application.ScreenUpdating = True
End Sub
 
Frank,
Mank thanks. It went in the blink of an eye. Not comprehending the
code, I made a parallel run with my code below, but into Column("O")
instead of Column("N").

It was identical, but I could not understand that this time the code
below, which was virtually unchanged, except for replacing ("N") with
("O") and the -3 to a -4 reference, and placing this edited part into
a 'new' sub, ran much faster, doing 1513 lines in 15secs, when before
it did only 328 lines. Any ideas why this happened? Could it be that
the original sub had alot of blocks commented out that VBA still
looks/runs through without executing the results?

Also, in trying to comprehend your code, have I guessed right that
doing away with the loop gained the most in speed reduction?

Also, with little mention of "Application.Calculation =
xlCalculationManual" in 'help', would it be the case that not using
this, each loop or row calculated would be written to harddrive before
continuing on?

Also, what does the line; " .Value = .Value " do?

Sorry, just trying to understand what is going on.

Much appreciated, thanks
Jon



Frank Kabel said:
Hi
try the following code
Sub fill()

Dim mylastRow As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
mylastRow = Cells(Rows.Count, "K").End(xlUp).Row
With ActiveSheet.Range("N1:N" & mylastRow)
.FormulaR1C1 = "=IF(COUNTIF(tvsymbols,R[0]C11)>0,1,"""")"
Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
-----Original Message-----

Sub fill()

Dim mylastRow As Integer
Dim i As Integer
Dim dupeSymb As String
Dim k As Integer
Dim dupes() As Integer
Dim m As Integer
'Dim tstSym As String

Dim y As Integer
Dim tvSymList() As String
Dim lastTvSym As Integer

Dim t As Integer
Dim w As Integer
Dim tvFind As Boolean

k = 0

' //////////this next block too slow:- does 328 rows in 15 secs
lastTvSym = Cells(Rows.Count, "A").End(xlUp).Row
ReDim Preserve tvSymList(3 To lastTvSym)
For y = 3 To lastTvSym
tvSymList(y) = Range("A1").Offset(y - 1, 0).Value
Next y

For t = 2 To mylastRow '- k
tvFind = False
For w = 3 To lastTvSym
If tvSymList(w) = Range("N1").Offset(t, -3).Value Then
tvFind = True
Range("N1").Offset(t, 0).Value = 1
Exit For
ElseIf w = lastTvSym Then
Range("N1").Offset(t, 0).Value = ""
End If
Next w
Next t

End Sub
Click to expand...
Click to expand...
 
Hi Jon
see comments in-line

Jon said:
below, which was virtually unchanged, except for replacing ("N") with
("O") and the -3 to a -4 reference, and placing this edited part into
a 'new' sub, ran much faster, doing 1513 lines in 15secs, when before
it did only 328 lines. Any ideas why this happened? Could it be that
the original sub had alot of blocks commented out that VBA still
looks/runs through without executing the results?
Click to expand...

Could be for several reasons. Sometimes very hard to understand why VBA
performance differs from run to run. Looking at some older posts it
seems for me that esp. Excel 2002 shows sometimes this kind of
behaviour.

Also, in trying to comprehend your code, have I guessed right that
doing away with the loop gained the most in speed reduction?
Click to expand...

This + disabling screenupdating and the automatic calculation

Also, with little mention of "Application.Calculation =
xlCalculationManual" in 'help', would it be the case that not using
this, each loop or row calculated would be written to harddrive before
continuing on?
Click to expand...

This disables the automatic recalculation of your sheet during the code
execution. Esp. if you have many formulas in your sheet (with volatile
functions like OFFSET) this significantly reduces code execution.
Importan note: Don't forget to enable the calculation at the end of the
macro :-)

Also, what does the line; " .Value = .Value " do?
Click to expand...

As I've inserted formulas first I want to replace the formulas
afterwards with just the result of the formula. This is what this
statement doesRegards
Frank
 
Thanks Frank,

Great help. Alot learnt, and the daily routine should now run with little input.

Regards
Jon
 
Back
Top