Hi
I'm trying to go through a range of cells in a worksheet and write a formula in each one. However I keep getting the error:
This is the current code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3,B5")) Is Nothing Then
Sheets("EmpRpt").Unprotect
With Sheets("EmpRpt")
If .Range("B3,B5").Value <> Empty Then
.Range("B7").Value = Evaluate("=INDEX(Set!B:B,MATCH(B5,Set!C:C,0))")
.Range("D3").Value = Evaluate("=INDEX(Set!I:I,MATCH(B5,Set!C:C,0))")
.Range("AP3").Value = Evaluate("=INDEX(Set!J:J,MATCH(B5,Set!C:C,0))")
.Range("AP3").Value = Evaluate("=INDEX(Set!F:F,MATCH(B5,Set!C:C,0))")
ResetCalc
End If
End With
End If
Sheets("EmpRpt").Protect Contents:=True, AllowFiltering:=True
StopCalc
End Sub
It keep fails at the line of
where the code assign the formula to the cell. I had replaced the formula with text but it still dont work. The range are used correctly as well. I will appreciated if there any help you can provide
.Range("D3").Value = Evaluate("=INDEX(Set!I:I,MATCH(B5,Set!C:C,0))")
Bookmarks