Worksheet_Calculate event run time error 5

[Limone]

Hi. I keep getting this error whatever I do in the workbook, Excel points to the "if not intersect" line. Can anyone help me?

Private Sub Worksheet_Calculate()
Dim Target As Range, rng As Range, c As Range, myMatch As Variant
If Not Intersect(Target, Range("TabellaContratti[Indirizzo]")) Is Nothing Then
Set rng = Intersect(Target, Range("TabellaContratti[Indirizzo]"))
Else Exit Sub        
For Each c In rng            
If Len(c.Value) = 0 Then                
c.Offset(0, 1).Value = "" 
Else                    
myMatch = Application.Match(Split(c.Value, " ")(UBound(Split(c.Value))), Worksheets("#SigleProvince").Range("TabellaProvince[Sigla]"), 0)                    
If Not IsError(myMatch) Then 
c.Offset(0, 1).Value = Application.Index(Worksheets("#SigleProvince").Range("TabellaProvince[Provincia]"), myMatch)
End If            
End If        
Next    
End If
End Sub

 

[offthelip]

It would appear that you are trying to refer to a named range in the intersect statement however it also appears to me that:

[COLOR=#333333]Range("TabellaContratti[Indirizzo]")[/COLOR]

is an illegal named range because you are not allowed brackets in a named range.

 

[Limone]

It would appear that you are trying to refer to a named range in the intersect statement however it also appears to me that:

[COLOR=#333333]Range("TabellaContratti[Indirizzo]")[/COLOR]

is an illegal named range because you are not allowed brackets in a named range.

It actually refers to a column table with "TabellaContratti" as the table name and "Indirizzo" and the name of the column within that table. Sorry for the italian. It usually never gives me problem when I refer to columns this way.

 

[steve the fish]

The problem is not to do with the range in the table which is perfectly fine syntax but the fact that the Target variable doesnt exist.

 

[Limone]

I know that the problem is about the target, but the code that I have works for events such as worksheet_change (the event gets triggered as long as what I change is within the expressed range). I'm not sure why it shouldn't in this case.

 

[offthelip]

I have just realised that you were using the worksheet calculate event, this event doesn't have the parameter "target" so target is undefined.

 

[Limone]

I think I've seen some istances of people using it on the internet. How do you suggest I achieve what I'm trying to do then? I don't have a definite target, I want the event to be triggered whenever a cell in a specific column gets calculated by worksheet_change event (there are no formulas in it).

 

[steve the fish]

If you have no formulas how are these cells changing value??

 

[Limone]

Sometimes someone manually does, sometimes change events inserts a value. If you're telling me worksheet calculate only pertains formulas that's ok.

 

[steve the fish]

If somebody changes it manually or change events changes the cell then both of these will trigger worksheet change.