• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

worksheet_change event help needed

hostile

New Member
Hi, I have a script that works, but I need help expanding its use.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
       If Target.Count >1 Then Exit Sub
       If Not Intersect(Target, Range("F3:F4")) Is Nothing Then
     With Target
           If IsNumeric(.Value) Then
          Application.EnableEvents = False
          Range("V" & .Row).Value = Range("V" & .Row).Value + .Value
          Application.EnableEvents = True
    End If
End With
End If
End Sub

This is an accumulator, and it works, but I need it to also do the same thing on the same sheet but for different target and range. Just like with F3:F4 to "V",I need F6:F7 to produce a result on "W"

Any help would be great. I have mucked with it for several hours trying to get it to work.
Thanks
__________________________________________________________________
Mod edit : post moved to appropriate forum …
 
Last edited by a moderator:
Hi ,

See if this is OK.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            If Target.Count > 1 Then Exit Sub
            If Intersect(Target, Range("F3:F4")) Is Nothing Then
              If Not Intersect(Target, Range("F6:F7")) Is Nothing Then
                  With Target
                      If IsNumeric(.Value) Then
                          Application.EnableEvents = False
                          Range("W" & .Row).Value = Range("W" & .Row).Value + .Value
                          Application.EnableEvents = True
                      End If
                  End With
              End If
            Else
              With Target
                    If IsNumeric(.Value) Then
                      Application.EnableEvents = False
                      Range("V" & .Row).Value = Range("V" & .Row).Value + .Value
                      Application.EnableEvents = True
                    End If
              End With
            End If
End Sub
Narayan
 
Hi ,

See if this is OK.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            If Target.Count > 1 Then Exit Sub
            If Intersect(Target, Range("F3:F4")) Is Nothing Then
              If Not Intersect(Target, Range("F6:F7")) Is Nothing Then
                  With Target
                      If IsNumeric(.Value) Then
                          Application.EnableEvents = False
                          Range("W" & .Row).Value = Range("W" & .Row).Value + .Value
                          Application.EnableEvents = True
                      End If
                  End With
              End If
            Else
              With Target
                    If IsNumeric(.Value) Then
                      Application.EnableEvents = False
                      Range("V" & .Row).Value = Range("V" & .Row).Value + .Value
                      Application.EnableEvents = True
                    End If
              End With
            End If
End Sub
Narayan
Hi, Narayan

Thanks for the response. It doesnt work. All it does is cancel the first target, range statement. Values are only accumalating in V on Row 6&7. I was really hoping this worked. I tried this once, just didnt add thr else statement.
 
Hi again. It doesnt work the way I thought it would. The way it works, it actually requires me to input a value. I was hoping I could use the script to see a value created in a cell and it capture and store that value as if I inputted the value myself.

Using this script on target, range I11, to V I get the result.
I want to use the second target, range to capture the result of a calculation that runs if a value is put into I11.

So I enter 1 in I11, and it creates a value in T11 of 2, based on a formula on T11. I want to capture T11 value and place it in O2, and it never change, no mattet the value change in T11. Right now If I change the value in T11, it adjust the captured data based on the formula on t11. Does this make sense?
 
Hi ,

I have not understood your requirement.

Please explain step by step what a user will do , and what you expect the code to do.

Narayan
 
I uploaded a file. To clear results for testing, reset value in V3 to 0 then reset value in F3 to 0 and start over. This will reset the P/L and Tax values. The end result is to keep a running total of tax collected per state. I also want to do the same thing for profit or loss in each state a sale takes place in . This all feeds another sheet but I didnt add that. This is just sample data.
 

Attachments

  • Book1.xlsm
    19.3 KB · Views: 4
Not sure, but try this (also comments in code).
 

Attachments

  • Chandoo26625Book1.xlsm
    22.5 KB · Views: 3
Assuming F3 is zero, and V3 is zero, and cells T3 and U3 are both 0.00..
Set Q2 to Tx
Enter 1 in F3
Cell G4 reduces by 1.
Cells P3 Q3 R3 calculate values
Cell T3 calculates a value of 2.43
Cell U3 captures value of Q3 2.20

Now go back to F3 and select 0
You will see the values in Q3 and T3 remain the same

Now Select 1 in F3
You will see the values in U3 and T3 increase according to the number selected in F3

Now Zero out F3 again, and Change Q2 to Oh
Go to F3 and Select 1

The result is it adjust the value amount that is supposed to be accumulating in T3 and U3 to meet the value of V3
So instead of Tax being 2.20 + The Tax for Oh which would be 2.00, it changes the value and calculates whats in U3 based on what is in V3. The code says to do that but that is why I wanted a script to capture the value in Q3 and accumulate in U3 no matter the option selected in Q2.

IF I could accomplish that then the next step would be to get those value to go to the row next to the item selected in Q2 down in V14 and V15 and then I could get T3 to show a high level view of the total tax collected while knowing how much tax was collected for each state.

Does this make better sense?
 
Not sure, but try this (also comments in code).
Hi p45cal. Your code almost does the trick. U3 is not capturing a value. T3 does what its supposed to if you drag the data down to the next row. Thanks for the comments. I was wondering if you could comment the parts. I think I understand whats happening but im sure. Also do you think a vlookup with an if statement would help me identify everytime Tx or Oh is selected with the tax values collecting on the row and column v14 and v15? Something like =if(Q2<>"",VLOOKUP(Q2,A:V1008,V14,) When collecting tax I should be able to identify the amounts and to which state it belongs. Thanks alot for the help. Happy Thanksgiving!
 
Hi p45cal. Your code almost does the trick. U3 is not capturing a value. T3 does what its supposed to if you drag the data down to the next row. Thanks for the comments. I was wondering if you could comment the parts. I think I understand whats happening but im sure. Also do you think a vlookup with an if statement would help me identify everytime Tx or Oh is selected with the tax values collecting on the row and column v14 and v15? Something like =if(Q2<>"",VLOOKUP(Q2,A:V1008,V14,) When collecting tax I should be able to identify the amounts and to which state it belongs. Thanks alot for the help. Happy Thanksgiving!

Funny thing. If I add another statement to Range("U" & .Row).Value of + Range("Q" & .Row).Value after the first one that is exactly the same minus the .Value * .Value, it works.
 
If I add another statement to Range("U" & .Row).Value of + Range("Q" & .Row).Value after the first one that is exactly the same minus the .Value * .Value, it works.
Difficult to understand the above; just quote your code as it stands now.
 
Your Code: Range("U" & .Row).Value = Range("U" & .Row).Value + Range("Q" & .Row).Value * .Value

Changed to: Range("U" & .Row).Value = Range("U" & .Row).Value + Range ("Q" & .Row).Value + Range("Q" & .Row).Value

And it worked.

Not sure why. I just tried it after staring at the stuff for a while. I literally have no experience with this. Best I can do with it is record macros.

In any event I thank the both of you for your help.

One more thing. I changed the sheet and updated the code to reflect that (page uploaded), and now I am trying to get it to record the independant values as they are created in another column.

When you look at the page you will see that instead of having a global setting applied to the column where you select a state, I have created a new column with a drop down for each row. It does the same thing, but I want to record the amount calculated next to it in the respective cells Y and Z, as well.

This way tax can be calculated and accumulate per STATE per purchase per any item.
 

Attachments

  • Book1test.xlsm
    21.1 KB · Views: 1
Your Code: Range("U" & .Row).Value = Range("U" & .Row).Value + Range("Q" & .Row).Value * .Value

Changed to: Range("U" & .Row).Value = Range("U" & .Row).Value + Range ("Q" & .Row).Value + Range("Q" & .Row).Value

And it worked.

Not sure why. I just tried it after staring at the stuff for a while. I literally have no experience with this. Best I can do with it is record macros.

In any event I thank the both of you for your help.

One more thing. I changed the sheet and updated the code to reflect that (page uploaded), and now I am trying to get it to record the independant values as they are created in another column.

When you look at the page you will see that instead of having a global setting applied to the column where you select a state, I have created a new column with a drop down for each row. It does the same thing, but I want to record the amount calculated next to it in the respective cells Y and Z, as well.

This way tax can be calculated and accumulate per STATE per purchase per any item.


A simpler question would be; How do I incorporate or format a conditional if statment in the code you two helped with so that I can capture values and accumulate them much like a sumif statement?
 
Back
Top