CODEWITHSUNDEEP
vbaexcel-formula
Kevin P.
Kevin P.

Reputation: 1053

Storing current cell value, adding formula then changing format to custom

I am attempting to have the current value of a cell stored in a variable, its new value changed to a formula, then formatting the cell with a custom type of the stored variable.

For example: Cell B3 says "Hello" "Hello" is stored in variable sValue Cell B3 gets changed to equal D3 (=D3) Custom Format Type is completed to still read "Hello"

However, I get a

Run-time error '13':

Type mismatch

On sValue.

Updated code based on Comments:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sValue As Long

If Not Intersect(Target, Range("B3:B41")) Is Nothing Then
    sValue = Target.Value
    Target.Formula = "=D" & Target.Row
    Target.NumberFormat = Chr(34) & sValue & Chr(34)
End If
End Sub

The reason for the offset is because when I hit ENTER it performs the script on the new cell, when I am intending it to be on the above row that I edited. Not sure how to get it to run on the current cell without changing the cell.

Upvotes: 0

Views: 265

Answers (1)

Scott Craner
Scott Craner

Reputation: 152515

as per the comments:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sValue As String
    
    If Not Intersect(Target, Range("B3:B41")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo safeout
        sValue = Target.Value
        Target.Formula = "=D" & Target.Row
        Target.NumberFormat = Chr(34) & sValue & Chr(34)
    End If
safeout:
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions