0

I have multiple values on cell ("F3:N3") that change dependent on user input on ("E18"). I am having trouble capturing the values before change. I would like the capture to happen on cells ("V8:AD8") Below is the code..

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("E18").Address Then

    Application.EnableEvents = False

    Dim sOldValue As String, sNewValue As String
    sNewValue = Target.Value

    Application.Undo

    Dim rOld As Range
     rOld = Range("F3:N3").Value

    Target.Value = sNewValue

    Range("V8:AD8").Value = rOld.Value

    Application.EnableEvents = True

  End If

End Sub
Tom K.
  • 1,020
  • 1
  • 12
  • 28
Leocodetwist
  • 61
  • 1
  • 10
  • Possible duplicate of [How do I get the old value of a changed cell in Excel VBA?](http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) – Mathieu Guindon Jun 30 '16 at 13:48
  • Also, `rOld = Range("F3:N3").Value` isn't allowed since `rOld` is a range. – Kyle Jun 30 '16 at 14:12
  • set rOld = Range("F3:N3"), move your code to worksheet_calculate – Nathan_Sav Jun 30 '16 at 14:48
  • @kyle so how would i change rOld to make it work – Leocodetwist Jun 30 '16 at 14:50
  • @Nathan_Sav i set rOld = Range("F3:N3") but why would i need to move my code to worksheet_calculale – Leocodetwist Jun 30 '16 at 14:59
  • Because you said that changing on cell affects others, so they will be changed on calculate, I am not sure when the change event is triggered. You can leave it in the change though, i'll have a look at what's going on. – Nathan_Sav Jun 30 '16 at 15:07
  • @Nathan_Sav input/change happens on the E18 so that's where the traget is? Values I want to capture are on F3-N3 and want to display them on V8-AD8 – Leocodetwist Jun 30 '16 at 17:41

0 Answers0