Extreme Lag!

J

jigglypuff

New Member
Joined
Mar 28, 2013
Messages
25
Hi guys, I am using a code that's making my Excel run slower than a turtle, is there a problem with its length or is it because all the codes are working with the same column at the same time? Can anyone help me ? Here is the code I'm talking about:

If Cells(Target.Row, 1).Value >= 40909 And Cells(Target.Row, 1).Value <= 40939 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$56)*1000)"
If Cells(Target.Row, 1).Value > 40939 And Cells(Target.Row, 1).Value <= 40968 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$57)*1000)"
If Cells(Target.Row, 1).Value > 40968 And Cells(Target.Row, 1).Value <= 40999 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$58)*1000)"
If Cells(Target.Row, 1).Value > 40999 And Cells(Target.Row, 1).Value <= 41029 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$59)*1000)"
If Cells(Target.Row, 1).Value > 41029 And Cells(Target.Row, 1).Value <= 41060 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$60)*1000)"
If Cells(Target.Row, 1).Value > 41060 And Cells(Target.Row, 1).Value <= 41090 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$61)*1000)"
If Cells(Target.Row, 1).Value > 41090 And Cells(Target.Row, 1).Value <= 41121 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$62)*1000)"
If Cells(Target.Row, 1).Value > 41121 And Cells(Target.Row, 1).Value <= 41152 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$63)*1000)"
If Cells(Target.Row, 1).Value > 41152 And Cells(Target.Row, 1).Value <= 41182 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$64)*1000)"
If Cells(Target.Row, 1).Value > 41182 And Cells(Target.Row, 1).Value <= 41213 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$65)*1000)"
If Cells(Target.Row, 1).Value > 41213 And Cells(Target.Row, 1).Value <= 41243 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$66)*1000)"
If Cells(Target.Row, 1).Value > 41243 And Cells(Target.Row, 1).Value <= 41274 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$67)*1000)"
'2013'
If Cells(Target.Row, 1).Value > 41274 And Cells(Target.Row, 1).Value <= 41305 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$39)*1000)"
If Cells(Target.Row, 1).Value > 41305 And Cells(Target.Row, 1).Value <= 41333 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$40)*1000)"
If Cells(Target.Row, 1).Value > 41333 And Cells(Target.Row, 1).Value <= 41364 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$41)*1000)"
If Cells(Target.Row, 1).Value > 41364 And Cells(Target.Row, 1).Value <= 41394 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$42)*1000)"
If Cells(Target.Row, 1).Value > 41394 And Cells(Target.Row, 1).Value <= 41425 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$43)*1000)"
If Cells(Target.Row, 1).Value > 41425 And Cells(Target.Row, 1).Value <= 41455 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$44)*1000)"
If Cells(Target.Row, 1).Value > 41455 And Cells(Target.Row, 1).Value <= 41486 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$45)*1000)"
If Cells(Target.Row, 1).Value > 41486 And Cells(Target.Row, 1).Value <= 41517 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$46)*1000)"
If Cells(Target.Row, 1).Value > 41517 And Cells(Target.Row, 1).Value <= 41547 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$47)*1000)"
If Cells(Target.Row, 1).Value > 41547 And Cells(Target.Row, 1).Value <= 41578 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$48)*1000)"
If Cells(Target.Row, 1).Value > 41578 And Cells(Target.Row, 1).Value <= 41608 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$49)*1000)"
If Cells(Target.Row, 1).Value > 41608 And Cells(Target.Row, 1).Value <= 41639 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$50)*1000)"



Thank you in advance!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Didn't try it because I didn't feel like reformatting that amount of code. Please put code between code tags. Makes copying from here to the editor a whole lot easier.

If I had to take a guess without trying it though. I would think it has something to do with setting formulas and calculating time for excel. There could be a better way to do it, or you can take a look at this....

Slow Macros: Speed up Excel VBA Macro Code via Manual Calculation
 
Upvote 0
Code: 
If Cells(Target.Row, 1).Value >= 40909 And Cells(Target.Row, 1).Value <= 40939 Then Cells(Target.Row, 4).Formula = "=if(OR(C:C="""",C:C=""#DIC/0""),"""",(C:C/Sheet1!$U$56)*1000)"
If Cells(Target.Row, 1).Value > 40939 And Cells(Target.Row, 1).Value <= 40968 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$57)*1000)"
If Cells(Target.Row, 1).Value > 40968 And Cells(Target.Row, 1).Value <= 40999 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$58)*1000)"
If Cells(Target.Row, 1).Value > 40999 And Cells(Target.Row, 1).Value <= 41029 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$59)*1000)"
If Cells(Target.Row, 1).Value > 41029 And Cells(Target.Row, 1).Value <= 41060 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$60)*1000)"
If Cells(Target.Row, 1).Value > 41060 And Cells(Target.Row, 1).Value <= 41090 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$61)*1000)"
If Cells(Target.Row, 1).Value > 41090 And Cells(Target.Row, 1).Value <= 41121 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$62)*1000)"
If Cells(Target.Row, 1).Value > 41121 And Cells(Target.Row, 1).Value <= 41152 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$63)*1000)"
If Cells(Target.Row, 1).Value > 41152 And Cells(Target.Row, 1).Value <= 41182 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$64)*1000)"
If Cells(Target.Row, 1).Value > 41182 And Cells(Target.Row, 1).Value <= 41213 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$65)*1000)"
If Cells(Target.Row, 1).Value > 41213 And Cells(Target.Row, 1).Value <= 41243 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$66)*1000)"
If Cells(Target.Row, 1).Value > 41243 And Cells(Target.Row, 1).Value <= 41274 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$67)*1000)"
   '2013'
If Cells(Target.Row, 1).Value > 41274 And Cells(Target.Row, 1).Value <= 41305 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$39)*1000)"
If Cells(Target.Row, 1).Value > 41305 And Cells(Target.Row, 1).Value <= 41333 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$40)*1000)"
If Cells(Target.Row, 1).Value > 41333 And Cells(Target.Row, 1).Value <= 41364 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$41)*1000)"
If Cells(Target.Row, 1).Value > 41364 And Cells(Target.Row, 1).Value <= 41394 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$42)*1000)"
If Cells(Target.Row, 1).Value > 41394 And Cells(Target.Row, 1).Value <= 41425 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$43)*1000)"
If Cells(Target.Row, 1).Value > 41425 And Cells(Target.Row, 1).Value <= 41455 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$44)*1000)"
If Cells(Target.Row, 1).Value > 41455 And Cells(Target.Row, 1).Value <= 41486 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$45)*1000)"
If Cells(Target.Row, 1).Value > 41486 And Cells(Target.Row, 1).Value <= 41517 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$46)*1000)"
If Cells(Target.Row, 1).Value > 41517 And Cells(Target.Row, 1).Value <= 41547 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$47)*1000)"
If Cells(Target.Row, 1).Value > 41547 And Cells(Target.Row, 1).Value <= 41578 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$48)*1000)"
If Cells(Target.Row, 1).Value > 41578 And Cells(Target.Row, 1).Value <= 41608 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$49)*1000)"
If Cells(Target.Row, 1).Value > 41608 And Cells(Target.Row, 1).Value <= 41639 Then Cells(Target.Row, 4).Formula = "=if(C:C="""","""",(C:C/Sheet1!$U$50)*1000)"


Sorry, now it's like a code! P.S.: The first line is wider than the other cause I was testing that code to see if could solve my problem, unfortunately there's nothing to do with it!

This is a code to insert a formula in a given cell based on a DATE. e.g: If a given cell was first modified in the month of December then the formula X will be inserted in the cell to the right of that cell. If it had been modified in the month of February a formula Y would be inserted instead.

Thank you in advance.
 
Last edited:
Upvote 0
Code: 
    Dim sFrm        As String

    Select Case Cells(Target.Row, "A").Value
        Case Is < 40909, Is >= 41639
            MsgBox "Oops"
        Case 40909
            sFrm = "=if(OR(C:C="""",C:C=""#DIC/0""),"""",(C:C/Sheet1!$U$56)*1000)"
        Case Is <= 40968
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$57)*1000)"
        Case Is <= 40999
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$58)*1000)"
        '...
        Case Is <= 41639
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$50)*1000)"
    End Select

    Cells(Target.Row, "D").Formula = sFrm
I'm skeptical about your formula with its full-column references.
 
Upvote 0
There is still some lag but now it's lot better!
Maybe I can change other formulas to this format so the lag can decrease even more!

Thank you very much.
 
Upvote 0
shg said:
I'm skeptical about your formula with its full-column references.
Click to expand...

I was wondering the same thing. I had a sumproduct routine that was populating many many cells with a variable length to cover the entire data so I had it looking at entire columns. It took 10 minutes to populate all the cells. I was able to determine (with code) only the cells sumproduct needed for each individual cell it was to populate and cut my macro time down to less than 30 seconds by passing it only the range it needed to find its data. It was an additional 20 lines of code (give or take) but cut down macro run time by huge proportions.

You might want to see if you can also cut down your range to only what is needed to do the task at hand.
 
Upvote 0
Code: 
 Dim sFrm        As String

   Select Case Cells(Target.Row, "A").Value
        Case Is < 40909, Is >= 41639
            MsgBox "Oops"
            
            Case 40909
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$56)*1000)"
            Case Is <= 40968
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$57)*1000)"
            Case Is <= 40999
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$58)*1000)"
            Case Is <= 41029
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$59)*1000)"
            Case Is <= 41060
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$60)*1000)"
            Case Is <= 41090
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$61)*1000)"
            Case Is <= 41121
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$62)*1000)"
            Case Is <= 41152
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$63)*1000)"
            Case Is <= 41182
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$64)*1000)"
            Case Is <= 41213
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$65)*1000)"
            Case Is <= 41243
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$66)*1000)"
            Case Is <= 41274
            sFrm = "=if(C:C="""","""",(C:C/Sheet1!$U$67)*1000)"
            

    End Select
    Cells(Target.Row, "D").Formula = sFrm



Everything was fine until I notice that the code put into any cell was always the same, and them it started to say that there is an error in the last line of this code.

What's going on? Is my excel with a problem or something like that??? Furthermore, now in this archive of the excel (and only in this one and in copies of this one) I cannot 'reset' the code after the error. e.g.: an error happens and then I am unable to reset the code, before I was able to reset the code and see if the problem had been solved but now to do so I have to RESTART my excel!

I am starting to think there is some problem with it =(
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Send specific data from one sheet to another when a condition is met,
Replies
7
Views
263
mumps
mumps
K
  • Solved
Incrementing cell address inside Filter function
2 3
Replies
20
Views
2K
Fluff
Fluff
H
  • Question Question
External Link issue with Tables
Replies
0
Views
333
hajiali
H
S
  • Question Question
Need Help in Dates
2 3
Replies
27
Views
1K
Dave Patton
D
L
  • Question Question
Code revision for transferring/clearing data between worksheets
Replies
4
Views
500
Lil Stinker
L

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top