I found a great answer to a previous question provided by @angelofdev to a problem of exchanging the user entered data with the result of a formula,HERE. The code works perfectly, but I am a little stuck. I tried commenting on that thread, but thought this might be better?

I need to apply this to a range of cells, e.g. (D1:D20), but if I enter this into his code, it doesn't work and I'm not sure where I'm going wrong?

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' The below If statement uses Intersect to check, 
    ' if the cell being changed is NOT cell D1 it gets ignored.
    If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then
        Application.EnableEvents = False 'Disables events to prevent endless loop

        On Error GoTo Finalise 'Re-enable events

        ' The below code gets the value from cell D1,
        ' and stores the value to the inputVal variable.
        inputVal = Range("D1").Value 

        ' The below code does your calculation, 
        ' and stores the value to the newValue variable.
        newValue = inputVal * 0.58

        'Changes the value in cell D1 to the value of the newValue variable. 
        Range("D1").Value = newValue 
    End If

    Application.EnableEvents = True
End Sub

Loop over the cells in the Intersection:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range, Intr As Range
    Set Intr = Intersect(Target, Range("D1:D20"))

    If Not Intr Is Nothing Then
        Application.EnableEvents = False
            For Each cell In Intr
                cell.Value = 0.58 * cell.Value
            Next cell
        Application.EnableEvents = True
    End If
End Sub

You don't need an intermediate variable. Include error-catcher if you think it is necessary.

  • This works perfectly thank you! – Smiley125 Nov 9 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.