Why does this Worksheet Calculate Event run so slow?

[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

 

[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

 

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

 

[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

 

[Don Guillett]

Maybe you would like to post your final code for the archives.

 

[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

 

[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

 

[Don Guillett]

If you have a lot of cells to clear if not, then just clear them first,
something like
columns("g").clearcontents
or
range("g1:g21").clearcontents