Object variable or With block variable not set

[Koen]

Hi, I have the following code, but I keep getting 'error 91: Object variable or With block variable not set'.

Does somebody have a solution to this?

Thank you very much!


The code is moving the value of A1 and B1 to A2 and B2, the value of A2 and B2 to A3 and B3, ... every time A1 changes. The value of A1 isn't entered manualy but comes over an OPC server.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect "koen"
    
    Dim c As Range
    c = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & c + 1).Value = Range("A1").Value
    Range("B" & c + 1).Value = Range("B1").Value

Me.Protect "koen"
End Sub

 

[VoG]

TRy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long

Me.Unprotect "koen"
    
    c = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & c + 1).Value = Range("A1").Value
    Range("B" & c + 1).Value = Range("B1").Value

Me.Protect "koen"
End Sub

 

[Koen]

Great!! Thank you very much .

But I had expected that my code would move the new value of A1 to A2, but instead it puts the value at the botom of the A collum, Do you have any solution to this one?

so it makes: 4123 (4 is in A1, 1 is in A2, 2 is in A3, 3 is in A4)
and not: 4321

I'm sorry, but like you probably already have noticed, i'm a newbie in vba .

Thank you!!

 

[VoG]

So should it be?

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect "koen"
    
    Rows(2).Insert
    Range("A2").Value = Range("A1").Value
    Range("B2").Value = Range("B1").Value

Me.Protect "koen"
End Sub

 

[Koen]

Thanks, I need to be at work to test it. Tomorow morning I will test it, but I think it will do .

Thank you very much!

 

[VoG]

Actually we need to turn off events or it will go into an infinite loop

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "koen"
    
    Rows(2).Insert
    Range("A2").Value = Range("A1").Value
    Range("B2").Value = Range("B1").Value

Me.Protect "koen"
Application.EnableEvents = True
End Sub

 

[Koen]

Works perfect!

Thank you very much!!

Koen,