# Extreme Lag!

#### jigglypuff

##### New Member
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)"

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### rjwebgraphix

##### Well-known Member
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

#### jigglypuff

##### New Member
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.

Last edited:

up

#### shg

##### MrExcel MVP
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``````

#### jigglypuff

##### New Member
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.

#### rjwebgraphix

##### Well-known Member

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.

#### jigglypuff

##### New Member
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 =(

#### shg

##### MrExcel MVP
Step through the code and see which case is being executed. See Debugging VBA

Replies
20
Views
390
Replies
4
Views
187
Replies
9
Views
191
Replies
4
Views
439
Replies
1
Views
482

1,195,700
Messages
6,011,196
Members
441,594
Latest member
AVO

### 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.

### Which adblocker are you using?

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

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