Calculation formula on Change by Val

**cajand** · 01-27-2015, 07:38 AM

Hi,

I use the code below push a calculation formula to U11:U.
I want to execute it with a change by val event. If a cell in the range R11:R511 is changed I want
to execute

 "=T11/S11"

.
However I only want the macro to set the formula on the same row as the cell that has been changed in R11:R511.

But I can't get it to work. Any help ?: )


Sub CalcTg()

Dim LastRow As Long
     
    LastRow = Cells(Rows.count, "O").End(xlUp).Row
    
        If WorksheetFunction.CountA(Range("O11:O511")) >= 1 Then
    
        sheets("Kalkyl").Range("U11:U" & LastRow).Formula = "=T11/S11"
    
        End If

End Sub

**TMS** · 01-27-2015, 08:12 AM

You need a worksheet change event handler in the sheet where you are making changes:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lLR As Long
Dim rInterest As Range, cell As Range

lLR = Range("R" & Rows.Count).End(xlUp).Row
lLR = IIf(lLR < 11, 11, lLR)         ' minimum range R11
Set rInterest = Range("R11:R" & lLR) ' minimum range R11

' ignore any changes outside the "range of interest"
If Intersect(Target, rInterest) Is Nothing Then Exit Sub

' for every cell in the range, add the formula
Application.EnableEvents = False
For Each cell In Target
    Range("U" & cell.Row).Formula = _
        "=IFERROR(T" & cell.Row & "/S" & cell.Row & ","""")"
Next 'cell
Application.EnableEvents = True

End Sub

Regards, TMS

**TMS** · 01-28-2015, 04:51 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.