Run time error '91'

[Patriot2879]

Hi i have the code below but i keep getting a runtime error 91 object variable or with block variable not set, please can you hellp with this it highlights in yellow when i click on debug the .Formula = TheFormula code which is below in the whole code, any advise would be great and thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Static Cell As Range
    Static TheFormula As String
    Set Rng = Range("N2:V192")
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Not Cell Is Nothing Then
            Cell.Formula = TheFormula
        End If
        Set Cell = ActiveCell
        With Cell
            TheFormula = .Formula
            .Value = .Value
        End With
    Else
        With Cell
            .Formula = TheFormula
        End With
    End If
End Sub

 

[BarryL]

When you open the workbook and select, lets say, A1 it will error because "Cell" is not set to anything but you are trying to treat is an object that is set to something.

I think your errors would go if you changed Cell with Target and removed Set Cell = ActiveCell also. Target is the activecell you just selected so no need to create another object for the activecell.

 

[Patriot2879]

Hi thanks for the help i have just changed the code to the following...but now when i click on the cell the data gets deleted. can i have some more advise please

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Static Cell As Range
    Static TheFormula As String
    Set Rng = Range("N2:V192")
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Not Cell Is Nothing Then
            Target.Formula = TheFormula
        End If
        With Target
            TheFormula = .Formula
            .Value = .Value
        End With
    Else
        With Target
            .Formula = TheFormula
        End With
    End If
End Sub

 

[BarryL]

you still have Cell referenced.

this:
If Not Cell Is Nothing Then

should be this:
If Not Target Is Nothing Then

and the Static Cell as range can be removed as well.

 

[Patriot2879]

hi i have now changed the code to the following... but it is still deleting the data when a cell is clicked on, please help. I am still quite new to this coding and still learning.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Static TheFormula As String
    Set Rng = Range("N2:V192")
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Not Target Is Nothing Then
            Target.Formula = TheFormula
        End If
        With Target
            TheFormula = .Formula
            .Value = .Value
        End With
    Else
        With Target
            .Formula = TheFormula
        End With
    End If
End Sub

 

[BarryL]

Just to clarify, what is it that you want the code to do?

 

[Patriot2879]

i think it was to hide the folrmula in the cell

 

[BarryL]

you want to essentially paste values over the formula so people cant see the formula in the N:V range?

does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Range("N2:V192")
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Not Target Is Nothing Then
            Target.Value = Target.Value
        End If
    End If
End Sub

 

[Patriot2879]

hi yes this works for me, many thanks much appreciated

 

[BarryL]

No problem, glad you got it sorted.