What is wrong with this code?

[PATSYS]

Private Sub worksheet_change(ByVal target As Range)

Dim rr As Integer

rr = target.Row

Cells(rr, 1).Value = "C " & rr

End Sub

I keep getting debug error on the line

Cells(rr, 1).Value = "C " & rr

Thanks

 

[Tom Urtis]

Could be that the target row is greater than 32,767.

Try declaring rr as Long instead of Integer and see what happens.

 

[PATSYS]

Hi Tom,

The target is cell B6.

I tried declaring it as long and as variant, still get the error message:

Runtime error (long digits)

Method 'value' of object 'Range' failed

 

[PATSYS]

Hi Tom,

I added

Application.EnableEvents = False

At the beginning and I stopped getting the error message.

I am just wondering though, because no other cells have formula that are dependent on B6. However there are some that are dependent thru their conditional formatting. But change in formatting does not constitute a worksheet_change event as far as I know...

 

[Tom Urtis]

rr should be declared as Long anyway, but in any case I tried your code and it works for me without errors. Something else must be going on in your worksheet or maybe some conflicting code. CF should have no bearing on the problem. I cannot duplicate your error.

 

[xenou]

Changing a cell creates a change event so you enter an infinite loop when you change cells during change events. The error message doesn't really make sense but the fact that there is an error is expected. In short, with Excel you have to disable events to change cells during change events.

ξ