Help about an automation i want to create

[damav78]

Hi, I would like your help in an issue that i cannot find a way to solve.
I have 2 cells.
The first one has a dropdown list with one option only available.
The second one is a formula cell that collects multiple data from other cells and its dynamic. It means that when i change values to other cells this cell is affected.
So what i would like to do is:
When the first cell is blank the values on second cell to change as normally do (dynamically).
If from the first cell i choose the predefined value from my dropdown list then the second cell to actually "lock" the current value that has at that moment and dosen't be affected anymore from any changes.
Thank you in advance

 

[mumps]

See if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
        Range("T" & Target.Row).Value = Round(Range("T" & Target.Row).Value, 4).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(K" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

 

[damav78]

See if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("Q" & Target.Row).Value = Range("Q" & Target.Row).Value
        Range("T" & Target.Row).Value = Round(Range("T" & Target.Row).Value, 4).Value
    Else
        Range("Q" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(K" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

I got an run-time error message 424

 

[mumps]

Which line of code is highlighted when you click "Debug"?

 

[damav78]

Which line of code is highlighted when you click "Debug"?

Range("T" & Target.Row).Value = Round(Range("T" & Target.Row).Value, 4).Value

 

[mumps]

It would be easier to help if you could upload a copy of your file, (de-sensitized if necessary), to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[damav78]

It would be easier to help if you could upload a copy of your file, (de-sensitized if necessary), to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Can i pm it to you?

 

[mumps]

Unfortunately, that is not possible. Forum rules state that all communication must be done in the thread you posted.

 

[damav78]

Unfortunately, that is not possible. Forum rules state that all communication must be done in the thread you posted.

Ok let me just upload only the sheet we are talking about. Because my file have some extra sheets.

 

[damav78]

Unfortunately, that is not possible. Forum rules state that all communication must be done in the thread you posted.

Dropbox

www.dropbox.com

Dear @mumps you will notice that i have add and delete one column. So the column Q is replaced by R, and column K is replaced by L

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("R" & Target.Row).Value = Range("R" & Target.Row).Value
        Range("T" & Target.Row).Value = Round(Range("T" & Target.Row).Value, 4)
    Else
        Range("R" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(L" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub