PDA

View Full Version : Help with inputting the value in the back sheet using VBA code



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

snb
04-22-2016, 03:05 AM
Please use code tags !

You'd better use a userform instead of a worksheet as inputform.


Private Sub Worksheet_Change(ByVal Target As Range)
if not 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) Is Nothing Then
For Each cll In target
If IsNumeric(cll) Then Worksheets("Data Sheet").Range(cll.Offset(108,choose(Range("Q5"),0,8,24,16,32)).Address) = cll.formula
next
End If
End Sub

maorlov
04-22-2016, 03:21 AM
thank you!