Why does this Worksheet Calculate Event run so slow?

D

DDawson

I'm trying to create a validation macro to enter text into the cell of range
G:G depending on the contents of the adjacent cells in columns C and D. This
is what I have done to start with, there are other ElseIf entries to be
added, but it is really slow to start with.

Private Sub Worksheet_Calculate()
Dim myC As Range
Dim WatchRange1 As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set WatchRange1 = Range("G2:G500")

For Each myC In WatchRange1

If myC.Offset(0, -3).Value = "Contract1" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response A"
ElseIf myC.Offset(0, -3).Value = "Contract 2" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response B"
Else: myC.Cells.Value = ""
End If

Next myC

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
 
D

Don Guillett

Putting into a calculate event calculates everything. I would write a bit
differently using cells(

for i= 2 to cells(rows.count,"g").end(xlup).row
if cells(i,"c")="sa" and cells(i,"d")="c1" then cells(i,"g")="aa"
if cells(i,"c")="sa" and cells(i,"d")="c2" then cells(i,"g")="bb"
if cells(i,"c")="sb" and cells(i,"d")="c1" then cells(i,"g")="aa1"
..etc with ONE line IFs with NO end if required for one liners
next i
 
G

Gary''s Student

Use the worksheet Change event rather than the Calculate event. Target will
allow you to examine only a single pair of cells rather than all the pairs of
cells.
 
D

DDawson

I've worked at it, but can't get the calculate event to stop, it just goes on
forever.

As a workaround I've changed this into a macro and created a button on the
page to trigger the event when required.

Basically, it's only required once, when the document opens, because the
data is pulled in from an csv file exported from another database. I want
column G to analyse the data and apply my status comments where required.

I thank you both, Don and Gary's Student, for your helpful feedback.

regards
Dylan
 
D

DDawson

'My actual macro is similar to the following, except I've swapped
'the sensitive information with "Contract 1", "Status A", "Response A" etc..

'"Response A" is my Status, or Action depending on
'the specific Contract and contract Status.

Private Sub CommandButton1_Click()
Dim i As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row

'Contract 1
If Cells(i, "D") = "Contract 1" Then
If Cells(i, "C") = "Status A" Then Cells(i, "G") = "Response A"
If Cells(i, "C") = "Status B" Then Cells(i, "G") = "Response B"
If Cells(i, "C") = "Status C" Then Cells(i, "G") = "Response C"
If Cells(i, "C") = "Status D" Then Cells(i, "G") = "Response D"

'Else: Cells(i, "G") = ""
End If

'Contract 2
If Cells(i, "D") = "Contract 1" Then
If Cells(i, "C") = "Status A" Then Cells(i, "G") = "Response E"
If Cells(i, "C") = "Status B" Then Cells(i, "G") = "Response F"
If Cells(i, "C") = "Status C" Then Cells(i, "G") = "Response G"
If Cells(i, "C") = "Status D" Then Cells(i, "G") = "Response H"
'Else: Cells(i, "G") = ""
End If

'..etc with ONE line IFs with NO end if required for one liners
Next i

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
 
D

DDawson

How do I clear the cells, if the Contract Changes to "Contract 3", or
"Contract 4" I've tried the follwoing with no success?

If Cells(i, "D") <> "Contract 1" Or Cells(i, "D") <> "Contract 2" Then
Cells(i, "G") = ""
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top