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

#### Bond00

##### Board Regular
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>

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Bond00

##### Board Regular
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:
Code:
``=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

##### Board Regular
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:
Code:
``=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:
Code:
``````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

##### Board Regular
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:
Code:
``=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:
Code:
``````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:
Code:
``````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

##### Board Regular
Try this version:
Code:
``````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.

Last edited:

#### Joe4

There is nothing currently in the code that prevents it from running on rows above 10. You could easily change that, i.e.
Code:
``    If Target.Column = 12 [COLOR=#ff0000]and Target.Row >= 10[/COLOR] Then``

Great thanks

Replies
1
Views
171
Replies
0
Views
258
Replies
3
Views
348
Replies
24
Views
1K
Replies
0
Views
169

1,148,294
Messages
5,745,936
Members
423,985
Latest member
sayed manzar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back