How to get the percentage value in the same cell with vba

Question

I have a range in sheet 7, Range("A1:A10"), I am trying below code to get a percentange value in the same Cell, but the Excel hung up whenever i type a number in Range("A1:A10"), and sometime, It gives me a runtime error "28" out of stack space. but can't show any line number or highlight the line.

I am trying this for example

If i type any number in the range A1 to A10, For example 1499 in cell A2, the Value should be change to 1338.46 in cell A2 (Same Cell), Because 1338.46 is 89.29% of 1499.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim rngCell As Range, m, v

For Each rngCell In Range("A1:A10")

        v = rngCell.Value

        If Len(v) > 0 Then

m = v * 89.29 / 100

If Not IsError(m) Then rngCell.Value = m

End If

Next

End Sub

You need to disable events to avoid an endless loop. – SJR Sep 29 '20 at 15:30 — SJR, Sep 29 '20 at 15:30
I guess you need to turn off `EnableEvents`. – Light Sep 29 '20 at 15:30 — Light, Sep 29 '20 at 15:30

score 1 · Accepted Answer · answered Sep 29 '20 at 16:13

Same Cell Calculation (Worksheet Change)

You have to turn off events before the calculation, and turn them on afterwards (or in case of an error).

The Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const rngAddress As String = "A1:A10"
    Const Perc As Double = 89.29
    
    Dim rng As Range
    Set rng = Intersect(Target, Range(rngAddress))
    
    If rng Is Nothing Then
        Exit Sub
    End If
    
    Dim rngCell As Range
    Dim v As Variant
    
    Application.EnableEvents = False
    On Error GoTo SafeExit
    
    For Each rngCell In rng.Cells
        v = rngCell.Value
        If VarType(v) = vbDouble Then
            rngCell.Value = v * Perc / 100
        End If
    Next

SafeExit:
    Application.EnableEvents = True

End Sub

Now it's time to change your Name from VBasic2008 to VBasic2050. Hats of Guru.. You are real Excel Champ. — Prabhat Vishwas, Sep 29 '20 at 17:11

How to get the percentage value in the same cell with vba

1 Answers1

Same Cell Calculation (Worksheet Change)