Getting compile error 91 object variable with block variable not set

Hi friends,
I want to insert a serial number in column ‘A’ if I delete a value from column ‘C’. The formula is working fine if enter manually in column ‘a’ but after adding it in a sheet module it is giving error 91 ‘ object variable with block variable not set’.

Please Login or Register  to view this content.

Any help will be appreciated.

Thanking you.

This event procedure:
Private Sub Worksheet_Change(ByVal Target As Range)

If for a particular worksheet, Target will come from the value that changed on the worksheet that triggered this event, if "ThisWorkbook.Worksheets("Information").Range("c:c"))" is not the same sheet that triggered this event that might be a problem. If it's from the same sheet you only need Range("C:C") none of the other stuff is required.

With this:
FormulaR1C1 = "=IF(RC[2]>0,SUM(MAX(R3C:R[-1]C),1),"""")"
you are not saying where the formula is going. For instance Range("A1").FormulaR1C1 =


With this:
Dim cell As Range
cell.Formula = cell.Value

Whenever you declare a range you have to then set or instantiate the range, meaning you have to tell Excel where that range is. Set cell = Worksheets("Sheet1").Range("A1"), you can't just declare it and start using it, because Excel doesn't know where it is.

I have amended the code:

Please Login or Register  to view this content.

The error has gone but not any output in column 'a'

Thank you.

Hi,
It's a revision of the code solved by you today. If I delete a title in column 'c' then I want to change the roll no. as per the formula. The formula is perfect so don't change it. Simply make it run in the code without error.
If I delete a title in column 'c' then the same roll number is transfer to the next student skipping the blank row.

I'm pasting the previous code here so that you may insert the formula in it to avoide two macros.

Please Login or Register  to view this content.

thank you.

I'm still uncertain as to what you are doing. This puts your formula in A4, so if you need further assistance. By the way your formula in A4 says If(C4>0 and C4 has text in it. If you want to check if C4 is empty you might want to change it to C<>"". Let me know if you need anymore help.
Good Luck!!!

Hi skywriter,
Thank you. = 0 and = "" are giving the same output without error. I accept your suggestion of using = "" instead of =0.
I appreciate you for your kind cooperation to solve my problem.
Thank you.