maorlov
04-22-2016, 02:44 AM
Hi all,
I have a problem with my code and hope someone can help me. The code in the worksheet is the below. The idea is that the user inputs value in one sheet and that is automatically transferred to the back "Data sheet". The code below is correct in that sense. Problem is that cell in the Data Sheet is a formula (sum of 2 cells) and when the user, for example, inputs the value in one cell from the input sheet to the "Data Sheet", all the formulas in the "Data sheet" become values. My question is how to keep them as formulas? So ideally, the cells that are not changed should remain the same, as formulas, as they were before the change.
Thank you!
Martina
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToProcess As Range
Set RngToProcess = Intersect(Range("D14:E16, D18:E20, D24:E26, D29:E31, D34:E36,C41:C43, F41:F43, D47:E49, D51:E53, D57:E59, D62:E64, D67:E69"), Target)
If Not RngToProcess Is Nothing Then
For Each cll In RngToProcess.Cells
If IsNumeric(cll.Value) And Range("Q5") = 1 Then Worksheets("Data Sheet").Range(cll.Offset(108).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 2 Then Worksheets("Data Sheet").Range(cll.Offset(108, 8).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 4 Then Worksheets("Data Sheet").Range(cll.Offset(108, 16).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 3 Then Worksheets("Data Sheet").Range(cll.Offset(108, 24).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 5 Then Worksheets("Data Sheet").Range(cll.Offset(108, 32).Address).Value = cll.Value
Next cll
End If
End Sub
I have a problem with my code and hope someone can help me. The code in the worksheet is the below. The idea is that the user inputs value in one sheet and that is automatically transferred to the back "Data sheet". The code below is correct in that sense. Problem is that cell in the Data Sheet is a formula (sum of 2 cells) and when the user, for example, inputs the value in one cell from the input sheet to the "Data Sheet", all the formulas in the "Data sheet" become values. My question is how to keep them as formulas? So ideally, the cells that are not changed should remain the same, as formulas, as they were before the change.
Thank you!
Martina
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToProcess As Range
Set RngToProcess = Intersect(Range("D14:E16, D18:E20, D24:E26, D29:E31, D34:E36,C41:C43, F41:F43, D47:E49, D51:E53, D57:E59, D62:E64, D67:E69"), Target)
If Not RngToProcess Is Nothing Then
For Each cll In RngToProcess.Cells
If IsNumeric(cll.Value) And Range("Q5") = 1 Then Worksheets("Data Sheet").Range(cll.Offset(108).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 2 Then Worksheets("Data Sheet").Range(cll.Offset(108, 8).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 4 Then Worksheets("Data Sheet").Range(cll.Offset(108, 16).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 3 Then Worksheets("Data Sheet").Range(cll.Offset(108, 24).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 5 Then Worksheets("Data Sheet").Range(cll.Offset(108, 32).Address).Value = cll.Value
Next cll
End If
End Sub