Why does this Worksheet Calculate Event run so slow?
 

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

Why does this Worksheet Calculate Event run so slow?
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DDawson" wrote in message
...
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

Why does this Worksheet Calculate Event run so slow?
 

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.
--
Gary''s Student - gsnu200769


"DDawson" wrote:

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