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

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
0
jonmac (22)
7/9/2004 7:43:28 AM
excel 39879 articles. 2 followers. Follow

4 Replies
417 Views

Similar Articles

[PageSpeed] 40

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-----
>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
>.
>
0
frank.kabel (11126)
7/9/2004 9:39:33 AM
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" <frank.kabel@freenet.de> wrote in message news:<2a44b01c46598$a41a7c10$a301280a@phx.gbl>...
> 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
0
jonmac (22)
7/10/2004 2:41:12 AM
Hi Jon
see comments in-line

Jon Macmichael wrote:
>> 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?

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?

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?

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?

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 does
>
Regards
Frank

0
frank.kabel (11126)
7/10/2004 6:17:18 AM
Thanks Frank,

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

Regards
Jon



"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:<u4RABWkZEHA.3228@TK2MSFTNGP12.phx.gbl>...
> Hi Jon
> see comments in-line
> 
> Jon Macmichael wrote:
> >> 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?
> 
> 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?
> 
> 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?
> 
> 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?
> 
> 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 does
> >
> Regards
> Frank
0
jonmac (22)
7/10/2004 10:26:52 AM
Reply:

Similar Artilces:

Running Office 2004 with network homes on 10.4
Hi. Im really at the end of my tether with office now, for well over a year it seems as though no matter what I do office just behaves like a spoilt brat. Were talking 10-20 minute load times just to load an office app. It will sit there and first it say configuring microsoft office components. then through a host of other things until eventually as i said about 20 minutes later it will actually open. I've seen things about fonts before, but i can tell its not just (if at all) this. I have a reasonably fast network, so i cant see how it is network issues. All clients have networ...

Make outlook run as if its the first time
Hello How do I make outlook run as if it is the first time? I messed with the settings... pleaseeeeeeee help! Guy >How do I make outlook run as if it is the first time? >I messed with the settings... pleaseeeeeeee help! Uninstall it and reinstall it. What settings did you change that no longer meet your requirements? Why not simply change them back? -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't speak for Smiths, and Smiths doesn't speak for me. "Br...

How do I place two or more charts on a worksheet?
I can't figure out how to keep Excel 2007 overlaying a new chart on top of an existing chart in a seperate worksheet. What I would like to do is create one 8.5 x 11 worksheet with four 3 x 5 charts on it. Select each chart and move it into position. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Rick@Draper" <Rick@Draper@discussions.microsoft.com> wrote in message news:082C5930-64F0-4A6E-958E-967BDCCDB9C9@microsoft.com... >I can't figure out how to keep Excel 20...

using common control version 6 in VC6
Hi all, I'm trying to use the common control version 6 in my MFC project, I've imported the manifest.xml adn specified 6 as the platform, but I still can't use the version 6 members of the NMLVCUSTOMDRAW structure in my custom drawn controls. for instance, when I tried to use clrFace of the structure, it would say 'clrFace' : is not a member of 'tagNMLVCUSTOMDRAW', and I've traced the problem to the definition of the strucfure itself, typedef struct tagNMLVCUSTOMDRAW { NMCUSTOMDRAW nmcd; COLORREF clrText; COLORREF clrTextBk; #if (_WIN32_IE...

two Global Address Lists
Is it possible to have one exchange 2003 server with two Global address lists? I have two companies with two different domains running off of one Exchange 2003 server and i want two Global address lists. I have created two Global address lists but they do not show up for some reason. Anyone know why? See the article: 822940 How to Manage Address Lists When You Host Virtual Organizations http://support.microsoft.com/?id=822940 Thanks, Richard Roddy Microsoft Exchange Support This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Fro...

Slow response to every change made in Excel
Help. I have been a long time user of Excel and have come accross a problem I can't solve. Am using Excel 2003 with XP Prof. This a new computer and my Excel worked with no problems when I first got it. On a recent trip I was making changes to a complex worksheet and all of a sudden every change I made would result in the appearance of the "egg timer" and I would have to wait for the program either to save the changes or recalculate the worksheet ... I am not if it was that or something else. Now I have to wait just about everytime I do anything to a spreadsheet. I a...

Right click Context Menu
2007 Home & Student Office. Vista Home Premium SP2 Just wonder why is it that right clicking on any Office program will not have Run As Admin in the Context Menu. IF I right click on the program > Properties > Shortcut tab > Advanced. The box in front of Run As Admin is grayed out. Any reasons why. It is not a problem. Just want to know. Thank you. t-4-2 ...

Outlook Read Email Does Not Run The Form
Hello - I have a custom Email form I have designed with a button on it. However, I notice that when I double click on a form in my inbox the custom form is loaded so that that I can read the Email on the custom form. But, the button won't work unless I then click Form, Run this form. Isn't there a way that I can make the button work automatically when the user reads their Email so that they can just click the button rather than click Tools, Run this form and then click the button. I'm guessing that maybe Microsoft did this so that scripts won't run but I'm wonderi...

Any Measurement Studio users around?
Hello out there, Measurement Studio offers several components (e.g. Slide or Thermometer) whose orientation can be changed. However, this is not done the normal way going thru the property grid, but rather they provide a link below to "Inverse orientation". All fine and good but I need to do this at run-time and lacking a property it's all magic. If anybody knows how to achieve this the info would be highly appreciated. Best regards Helmut Giese Answering my own post (if anyone ever searches the archive for a similar question) the answer can be found here: http:/...

Money is Already Running #2
Quite often when I go to start Money I get an error telling me that it is already running. I had opened Money earlier in my Windows session but had closesd it. The only way I can re-open Money is to stop two processes in Task Manager related to Money. Anyone else experience this and what gives? There is a process(es) that under certain circumstances (unknown) won't die in a timely manner. I do know that MS are aware but are having trouble pinning down the exact cause. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.as...

Running out of space #2
Using exch 2000 server. I had 100 GB on a RAID card, but now I am only left with 4 GB, after 6 months of usage? When I look at the mail box usage, it does not add up to that. When I use exmerge, it only needs 27GB to backup all data. I am not using too much public folders etc. MDBData folder is 95.5 GB? How can I control this crazy swelling of data? And how do I delete the data that is not required anymore? Thanks Check the application event log. There will be some Event ID 1221 entries. 4 per night for the public and 1 per night for the private. How much free space does it report? If...

Update Links Slow
My supervisor just showed me a monthly process I am to inherit. Basically it involves these steps: 1. Copy Last month's files to This month's folder. 2. Rename the files. 3. Update the links to point to the new-name files instead of the files in last month's folder with last month's name. She says that to do this, it is faster if you copy everything to the local hard drive, update the links to refer to local files. Refresh the links. Then upload back to the network and change the links to refer to network files and then refresh the links again. She said t...

Running MFC dll from dos
I have a dll created using static linked mfc. When called the dll displays a modeless dialog. The dll needs nothing from the app after it is started. The dialog deletes it's memory when the dialog is destroyed. The dll runs from a windows app but not correctly from a dos app. When the dialog is executed from dos, the dialog doesn't seem to get the focus. The wait cursor is always display. The dos program has a while loop waiting for the dialog to finish it's operation. I'm sure this isn't right but wondering if someone could help on the proprer way to run from dos. Thanks ...

Slow Performance #3
Outlook is performing very so. It takes approx. 5 seconds to open an email. All other apps. open 1 second. I know I have enough memory and a fast processor. Problem began last week. Help! ...

slow Exchange while groupshield
Hello Since the newest DAT files our exchange server is very slow. Outlook reports 'requesting data...' very often. If I stop groupshield everything works fine again. store.exe consumes around 25% CPU all the time and has about 60000 pages faults per second. Exchange Version 2000 SP3 Groupshield version 5.20.689 engine version 4.4.00 dat version 4409 any ideas? thanks Thomas Sometimes the anti-virus packages will run a full scan of the mailbox stores after a new definition package is downloaded. You can check with your AV vendor and turn this feature off. The scan will run around ...

Upgraded to Exch2003, OWA2003, but experience is slow
I've setup a two node Exch2003 cluster. I still have Exch2000 running in co-existence with the new Exch2003 system. THen I create a new mailbox on the new Exch2003 cluster back-end servers. Via OWA, I logon with newUserAccount and I see the OWA 2003 interface. However, it comes slow and it doesn't finalize all the grapics. I try to send an e-mail, but I click "New" and nothing appears. Something is not working right. Any idea of what could it be ? I did not configure the steps below outlined the Exch2003 Deployment guide: Creating the HTTP Virtual Servers in Exchange...

Run Time Error 3711
I get this run error occsionally on a machine with Store Operations Manager running XP Pro. Run Time Error 3711 I would appreciate any help. Tony ...

Error when Running Sales Reconcile
I am trying to run the Sales Reconcile, and am getting a Unhandled script exception: Value of range. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE SCRIPT_CMD_SELECTED error, and I do not know why. Could it be a security issue? I am logged in as sa, but still receive the error.. Do you have access to partnersource or customer source the error is coming due to some value / percision in your database have you ever done any backend level modification in your database. Try to run Check Link for ItemMaster and Sales WOrk Transaction "steve" wrote: > I am trying to run the Sales Reconci...

Slow Performance
When opening the PO Entry or Receiving Entry window, and entering or selecting a transaction, the screen hangs for about 30 seconds before allowing entry. We are using Project Accounting in v9.0. This has started happening recently for no apparent reason. It is happening for all users on all workstations including sa. I tried: - Rebooting SQL Server - pointing Dynamics.set locally and deleting all modified reports, form and VBA - run Checklinks on Purchasing Transactions Any ideas? Regards Konrad Konrad, One item that can slow down particular screens is the autocomplete feature. ...

Two docs on one page
I have created an A4 document with two A5 labels on it. The first sheet has label one and two upon it but when i go to sheet two i would like it to have the next two labels upon it but it has label two again and three, sheet three has three and four. I have tried starting with next record before label one (ie staring at label two to force it to push to next label at the beginning of the sheet but this didnt help) i have also tried the same at the bottom of each sheet..... please can anybody help I have created an A4 document with two mail merged A5 labels on it using an ex...

Sum of data with two criteria
Hi there, i have a (simple!?) problem with the following.. In my sheet, i have 3 columns: column A, containing a order-number column B, containing a quota column C, containing a week-number Now what needs to bee counted, is the SUM of the quota (column B) occurences from a specific order, AND a specific week! Problem is, the rows can contain multiple occurences of an ordernumber... I'm feeling quitte stupid, can anyone help me please? :confused: --- Message posted from http://www.ExcelForum.com/ Hi try =SUMPRODUCT((A1:A1000=order_number)*(C1:C1000=week_number),(B1:B1000)) Fr...

background commands running in Excel?
I have created a new spreadsheet for some reporting that involves many charts that feed off one worksheet within the workbook. Every time that I open the workbook, the chart tab names are reset to "chart 1" "chart 2" etc. Also, even if I open the workbook and immediately close it, it asks if I want to save changes. The file is NOT linked to any external files. Is there any way that I can see the internal processes occuring that are changing these tab names and triggering the "save changes" command? Do you have an VBA code running in the workboo...

Windows 2000 cleints view the calendar incorrectly after tzmove is run
The wierd part of this is they show up with odd times. I have a user that shows all appointment off by 6 hours and 33 minutes for example. I think the time bias one the local machine was wrong when the tzmove tool was run. Now I need to find out if there is a way to force tzmove to update the meetings. I have confirmed the timezone information in the registry is correct but when I try to run tzmove it tells me there are no meetings to change but the meetings still apear wrong. Other thing I noticed is if I open the meeting the summary text in the middle of the meeting is correct but location...

How do I run a SQL query against a MS SQL Server database from exc
How do I run a SQL query against a MS SQL Server database from within Excel 2002? I know it can be done somehow using the SQL.Request addin function. However my attempts using this function have continually failed. Can some one please provide a working example to the pubs or Northwind dbs that I can work with and use to help debug my attempt? Thank you! ...

Problem when running Exchange Calendar Update Tool
I ran the Exchange update tool against a few mailboxes, and I am noticing a problem. The tool appeared to work to some extent, but one of the mailboxes I ran it against has some items modified and some not modified. Two recurring meetings that this user created and invited other people to were modified properly and updates were sent out automatically to the other people in the meeting. However, two other recurring meetings that he initiated and invited other people to were not modified and no update messages were sent for those meetings. Appointments that he created just for himself...