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

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
70
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%)

GHIJKLMN
211L2.22
4124.48
5135.65
7117.77
8128.96
91210.08

<tbody>
</tbody>
 

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,142
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 11, 2017
Messages
70
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,142
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 11, 2017
Messages
70
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,142
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 11, 2017
Messages
70
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,142
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top