Worksheet_Change with SUMIF Accumulator

[cptnrsk]

Hi,

I am working on a project with an accumulator and have found I need to include some form of SUMIF in VB to get values which are created, to collect and accumulate seperately.

This is the CODE:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F3:F1008")) Is Nothing Then
        With Target
            If IsNumeric(.Value) Then
               If .Value > 0 Then
                Range("W" & .Row).Value = Range("W" & .Row).Value + .Value
                Range("V" & .Row).Value = Range("V" & .Row).Value + .Range("R" & .Row).Value + Range("R" & .Row).Value
                Range("U" & .Row).Value = Range("U" & .Row).Value + Range("P" & .Row).Value - Range("M" & .Row).Value * .Value - Range("O" & .Row).Value
                Application.EnableEvents = True
           End If
           End If
           End With
      End If
End Sub


/CODE

This is the SUMIF Formulas I would like to embed above

=SUMIF(Q3:Q8,"Oh",R3:R8) which is entered on Y3
=SUMIF(Q3:Q8,"TX",R3:R8) which is entered on Z3

And I need cells Y and Z to accumulate what shows up in Range R3:R8, OH, or TX is selected in the Range Q3:A8.

Any ideas?

I currently have the accumulator working where it adds up the values in the Range R3:R8 and places them on the same row they are found in on Columns U,V,W, as can be seen in the code above, however I need to distinguish the values based on the SUMIF statements above.

Thanks for any help
C

[pecoflyer]

Hi and welcome

in the future please wrap code with code tags as per forum rules. i will do it for you this time

[NoSparks]

Code: Select all

Range("Y3").FormulaR1C1="=SUMIF(RC[-8]:R[5]C[-8],""Oh"",RC[-7]:R[5]C[-7])"

How to come up with that ?
Enter your =SUMIF(Q3:Q8,"Oh",R3:R8) formula into Y3 and hit Enter
Select Y3
Go to the VBA Immediate window and type ? activecell.FormulaR1C1 and hit Enter
What gets displayed is what you need in VBA code but you must double up the quote marks.

[cptnrsk]

It didnt work out well. Spent all day yesterday trying to use this method and came up short.

[cptnrsk]

Hi and welcome

in the future please wrap code with code tags as per forum rules. i will do it for you this time

No problem. Does this site support uploading the files you are working with so people who are helping can see what one is attempting to do?

[cptnrsk]

It didnt work out well. Spent all day yesterday trying to use this method and came up short.[/quote]

Is there a way to nest IF Q3="TX" in this

Code: Select all

Range("Y" & .Row).Value = Range("Y" & .Row).Value + .Value

[NoSparks]

Please don't quote entire posts, it's not necessary.

To attach a file, below the text box where you typed your questions there is an upload attachment tab, click that and browse to the file on your computer and add the file.

[cptnrsk]

Please don't quote entire posts, it's not necessary.

To attach a file, below the text box where you typed your questions there is an upload attachment tab, click that and browse to the file on your computer and add the file.

I figured it out. Thanks anyways.