What is wrong with this code?

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Could be that the target row is greater than 32,767.

Try declaring rr as Long instead of Integer and see what happens.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.

ξ
 
Upvote 0

Forum statistics

Threads
1,207,286
Messages
6,077,533
Members
446,288
Latest member
lihong3210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top