VBA code takes to much time to run!

[McQuinge]

Hi!
I'm using excel 2003 english version in windows 7.

I have a paperform where we take notes of different things through a period. My wish is to have the same information saved on a spreadsheet, so it would be easy to search through for information and print out a new updated paperform if we looses the original.

So I have made two sheets, one with the paperform layout called FORM 1 and one with all the values called DATABASE.
2500 rows with a unique ID for each paperform and 200 columns where the different values will be recorded to.

When a user wants to update my thought was that he could do it best on the paperform sheet, by just picking from a drop down list what paperform he wants to update or look at.
I have made a cell with validation format using UNIQUE_ID range as a list, When the user changes this he will get the right values in the paperform from the DATABASE sheet.
I have place this formula in each cell I want to update:

"=INDEX(DATABASE_FIELD;MATCH(SEARCH;UNIQUE_ID;0);MATCH("INFO 1";DATABASE!$1:$1;0))"

And so long this is working perfect!

But the trouble comes when I want to send or update the new values from the paperform sheet into the database. I have made a update button with the following command:

Private Sub cmdUPDATE_Click()
Dim vreg As String, drow As Integer, c As Range

'SEARCH is cell B3, and is used in FORM 1 to choose what row I want to work with or look at.
'SEARCH has a validation setting, saying that I can choose from a list called UNIQUE_ID.
    Let vreg = Range("SEARCH").Value

    With Sheets("DATABASE").Range("UNIQUE_ID")
        Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
             drow = c.Row
        End If
    End With
           
    With Me
    
'Gathering information from the sheet FORM 1. into the sheet DATABASE.
'If any of the cells have been changed in FORM 1, they will be updated with the new values into the DATABASE sheet.
        Sheets("DATABASE").Cells(drow, 2).Value = Range("D5").Value
        Sheets("DATABASE").Cells(drow, 3).Value = Range("D7").Value
        Sheets("DATABASE").Cells(drow, 4).Value = Range("D9").Value
        Sheets("DATABASE").Cells(drow, 5).Value = Range("G5").Value
        Sheets("DATABASE").Cells(drow, 6).Value = Range("G7").Value
        Sheets("DATABASE").Cells(drow, 7).Value = Range("G9").Value
        Sheets("DATABASE").Cells(drow, 8).Value = Range("E13").Value
        Sheets("DATABASE").Cells(drow, 9).Value = Range("F13").Value
        Sheets("DATABASE").Cells(drow, 10).Value = Range("E16").Value
        Sheets("DATABASE").Cells(drow, 11).Value = Range("F16").Value
        Sheets("DATABASE").Cells(drow, 12).Value = Range("E19").Value
        Sheets("DATABASE").Cells(drow, 13).Value = Range("F19").Value
        
'If cells in FORM 1 have been changed, they will have lost their formula, and this code will bring it back to the default.
'DATABASE_FIELD is the the whole range in DATABASE sheet with information.
        Range("D5").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 1"",DATABASE!$1:$1,0))"
        Range("D7").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 2"",DATABASE!$1:$1,0))"
        Range("D9").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 3"",DATABASE!$1:$1,0))"
        Range("G5").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 4"",DATABASE!$1:$1,0))"
        Range("G7").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 5"",DATABASE!$1:$1,0))"
        Range("G9").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 6"",DATABASE!$1:$1,0))"
        Range("E13").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 7"",DATABASE!$1:$1,0))"
        Range("F13").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 8"",DATABASE!$1:$1,0))"
        Range("E16").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 9"",DATABASE!$1:$1,0))"
        Range("F16").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 10"",DATABASE!$1:$1,0))"
        Range("E19").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 11"",DATABASE!$1:$1,0))"
        Range("F19").Formula = "=INDEX(DATABASE_FIELD,MATCH(SEARCH,UNIQUE_ID,0),MATCH(""INFO 12"",DATABASE!$1:$1,0))"
    
    End With
End Sub

have been reading and testing others VBA codes and now I am very pleased that I have got it to work. But it's turned out to be very slow solution.

Also the next operation that I have, turning back the paperform to it's original with all it's formulas is very slow.

So I'm hoping that my code are containing something useless that I can skip so it will get faster.

To make an update now it will take about 30 sec before it's done.

So don't hesitate to tell me how stupid I have been!

 

[GlennUK]

Switch off calculation and screenupdating at the beginning of your macro, and switch them on again at the end.

 

[ukmikeb]

Hi

This might be a bit of a red herring but I would be inclined to rename the Range("SEARCH") as SEARCH is a reserved word in Excel.

hth

 

[McQuinge]

Thanks for a fast answer and a good tip GlennUK!
It reduced the process time to 15 sec.

I'm still hoping that it is possible to reduce it even more.
And I'm willing to change the way I've built it to get it faster. It's just my knowlegde to VBA that is so poor that I can't see other ways to make it work.

ukmikeb, I'll change the SEARCH range to ACTUAL, but I haven't had any trouble with it I think...

Thanks!

McQuinge

 

[xenou]

Your original post mentions 2500 rows and 200 columns but I don't see nearly that many formulas in your code. If there are in fact 500,000 lookup formulas to calculate (2500x200) then the slowness is probably a matter of calculation time which cannot be changed by tweaking the code.

ξ

 

[McQuinge]

Hi xenou!

I've forgot to tell that the code is from an example I've made. I made a new spreadsheet just to make it easier to explain, and to attach it here, but that wasn't possible.
So it is 2500x200 but I don't think the code have to do 500,000 formulas at on time.

When a user pick a UNIQUE_ID it is the same as selecting one specific row. And then the code just need to send the 200 different values to the respective columns in that row.
it also seems like it takes long time just to put the formulas from the code back to the respective cells in the Paperform sheet. Is it another way to do this?

Thanks

McQuinge

 

[McQuinge]

Switch off calculation and screenupdating at the beginning of your macro, and switch them on again at the end.

Sorry, but I read through my thread again and suddenly someone had changed your post GlennUK (okey, I didn't see the calculation thing the first time I read your post). So when I did this the process now only takes 2 sec, and I'm very pleased.

Thanks again!

The last question for this thread now is; how can the thread be marked with solved?: confused:

McQuinge

 

[xenou]

The last question for this thread now is; how can the thread be marked with solved?: confused:

We don't used "solved" markers at this forum, so generally best thing to do is just post a reply mentioning you've got it working now and a thanks - just as you've done.

ξ