worksheet_calculate question

[DocBrown]

In the worksheet_calculate function I added code that analyzes data in some
cells, and sets the value of a specific cell. The code is as follow:

Private Sub Worksheet_Calculate()
Dim LookupDept As Range
Dim result As Variant

If Selection.Cells.Count = 1 And (ActiveCell.Column = 6 Or ActiveCell.Column
= 7) Then
Set LookupDept = Worksheets("Lists").Range("LookupDeptCode")

ActiveSheet.Range("N9").Value =
Application.VLookup(ActiveSheet.Range("F30"), LookupDept, 2, 0)

End If

End Sub

The "LookupDeptCode" is a named range with two columns where I'm extracting
the value of the second column based on the contents of cell "F30".

What I'm finding is that routine is being called recursively at the line
where I assign a value to cell "N9". Why is this occuring and how do I code
this routing so that doesn't happen?

This code is still experimental and incomplete as I learn how the
worksheet_calculate function works. But the idea is to set a cell based on
the contents of two other cells and the values in a lookup table. I know this
can be done with a formula in the cell, but I want to populate the cell but
still allow the user to set the value of the cell without wiping out the
formula.

Thanks,
John

 

[Don Guillett]

I'm not sure why the calculate event but try

 

[Don Guillett]

Private Sub Worksheet_Calculate()
If Selection.Count > 1 Then Exit Sub
If ActiveCell.Column = 6 Or ActiveCell.Column = 7 Then
Application.EnableEvents = False
Range("h30").Value = _
Application.VLookup(Range("F30"), [lookupdeptcode], 2, 0)
'Range("h30").Formula = _
'"=VLookup(f30,lookupdeptcode, 2, 0)"
Application.EnableEvents = True
End If
End Sub

 

[Gord Dibben]

Disable events in the code.

Private Sub Worksheet_Calculate()
Dim LookupDept As Range
Dim result As Variant

If Selection.Cells.Count = 1 And (ActiveCell.Column = 6 _
Or ActiveCell.Column = 7) Then
Application.EnableEvents = False
Set LookupDept = Worksheets("Lists").Range("LookupDeptCode")

ActiveSheet.Range("N9").Value =
Application.VLookup(ActiveSheet.Range("F30"), LookupDept, 2, 0)

End If
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[DocBrown]

Thanks Gord and Don.

The Application.enableEvents = False

does the trick.
John