Need Help with VBA script to make a value not change that is dependant on another Cell value

[Bond00]

So i need a way to have values in N column not change if i have an "L" in column L. values in N come from values in G + G*J% so if i change value in G from 2 to 22 it then changes value in corresponding cell in column N. i was thinking if i had some vba code to always copy the value from N to some other column like Q for example (a hidden column) and i could put a if then in column N so if L10="L" then N10=Q10 else SUM(G10+G10*J10%)

what is a simple vba script to copy N values from N10-N125 to Q10-Q125 but then not copy them if L10-L125 = "L" ? i think that would do the trick


Values in N have this going down =SUM(G10+G10*J10%)

G	H	I	J	K	L	M	N
2			11		L		2.22
4			12				4.48
5			13				5.65
7			11				7.77
8			12				8.96
9			12				10.08

<tbody>
</tbody>

 

[Bond00]

Still need help with this, anyone?

 

[Joe4]

Firstly, you do not need SUM in your formula. SUM and addition (+) are redundant. You only need one or the other, not both.
So, your formula in column N could look like:

=IF(L10="L",Q10,G10+G10*J10%)

What you want to do can be done in VBA, but the order of events is going to be very important here.
The would need to update column L BEFORE updating column G to "capture" the value and move it over to column N.
Are we sure that is going to happen that way?
If so, we can write VBA code that is automatically triggered when column L is updated to an L and copy the value over.

Does that sound like it would work for you?

 

[Bond00]

That would be perfect

yeah the values will be in place already and they will type L before changing any values in G. So we just need whatever value was already in N to stay the same after the value in G changes if there is a L next to it.
should be a pretty simple vba code snippet i just don't know vba syntax well enough to produce it myself lol

thanks appreciate it!

 

[Joe4]

OK, first off, minor edit to our formula. We need to check column Q, not L, or else the timing won't work right.
So it should be:

=IF(Q10<>"",Q10,G10+G10*J10%)

Now, we can use some automated VBA code that runs based on the putting an "L" in column L.
To make sure this is put in the proper place, do the following:
Right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column L is updates
    If Target.Column = 12 Then
'       Only run if column L populated with "L"
        If Target = "L" Then
'           Copy value of column N to column Q
            Target.Offset(0, 5).Value = Target.Offset(0, 2).Value
        End If
    End If
            
End Sub

This should do what you want.

 

[Bond00]

OK, first off, minor edit to our formula. We need to check column Q, not L, or else the timing won't work right.
So it should be:

=IF(Q10<>"",Q10,G10+G10*J10%)

Now, we can use some automated VBA code that runs based on the putting an "L" in column L.
To make sure this is put in the proper place, do the following:
Right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column L is updates
    If Target.Column = 12 Then
'       Only run if column L populated with "L"
        If Target = "L" Then
'           Copy value of column N to column Q
            Target.Offset(0, 5).Value = Target.Offset(0, 2).Value
        End If
    End If
            
End Sub

This should do what you want.

Thanks for the help.

But one added question, If i'm only checking column Q being empty or not "" then what if i delete the "L" from the L column? I need it to then default back to getting that value normally, no longer pulling it from the Q column from being locked. So if its doing a if check on the Q column it would then need to clear that Q cell when i delete the L value right? or is there a better way?

 

[Joe4]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column L is updated
    If Target.Column = 12 Then
        Select Case Target
'           If column L populated with "L"...
            Case "L"
'               ...copy value of column N to column Q
                Target.Offset(0, 5).Value = Target.Offset(0, 2).Value
'           If column L is deleted...
            Case ""
'               ...delete value from column Q
                Target.Offset(0, 5).ClearContents
        End Select
    End If
            
End Sub

 

[Bond00]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column L is updated
    If Target.Column = 12 Then
        Select Case Target
'           If column L populated with "L"...
            Case "L"
'               ...copy value of column N to column Q
                Target.Offset(0, 5).Value = Target.Offset(0, 2).Value
'           If column L is deleted...
            Case ""
'               ...delete value from column Q
                Target.Offset(0, 5).ClearContents
        End Select
    End If
            
End Sub

Works perfect, thanks!

Also i noticed it doesn't do it on cells above row 10 (which is what i wanted) but how did you do that? i don't see anything in the code that would indicate that? Just curious in case i ever need to change anything in the future.

 

[Joe4]

There is nothing currently in the code that prevents it from running on rows above 10. You could easily change that, i.e.

    If Target.Column = 12 [COLOR=#ff0000]and Target.Row >= 10[/COLOR] Then

 

[Bond00]

Great thanks