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