Sumproduct and Beyond

[Guest]

Hello
I am relatively new to VB and need help. I am not quite sure how to
accomplish this.... I need routine that will find the first blank cell in
Col H and insert a sumproduct type formula. That will sum all of the cells
in Col H where the value in the same row in col G is equal to "o" and the
value in col F contains "F". I also need to replicate this over 20 columns in
the same row. I am still trying to learn how to use sumproduct so if the
formula use would be different, what would this be?
Thanks

 

[Bob Phillips]

This does it for H,

Dim iLastRow As Long

iLastRow = Range("H1").End(xlDown).Row
If Range("H1").Value <> "" Then
Cells(iLastRow + 1, "H").Formula = _
"=SUMPRODUCT(--(F1:F" & iLastRow & "=""F"")," & _
"--(G1:G" & iLastRow & "=""o""))"
End If


but a couple of questions.

What if H6 is the first blank in H, but F10 and G10 have values?

If you want to extend to say I, J, K the data will be overlappiong. What are
the other columns?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

This macro does the first part of the job, but it's not clear for me what do
you want to replicate? This Sumproduct formula with the same column
references (H,G,F) or with other column references (if so, which columns)?

Sub sumprodtest()
Columns("H:H").Find(What:="*", _
After:=Range("H1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Activate
lastfilled = ActiveCell.Row
Range("H" & lastfilled + 1).FormulaR1C1 = _
"=SUMPRODUCT(R[-" & lastfilled & "]C:R[-1]C,--(R[-" & lastfilled &
"]C[-1]:R[-1]C[-1]=""o""),--(NOT(ISERROR(SEARCH(""F"",R[-" & lastfilled &
"]C[-2]:R[-1]C[-2])))))"
End Sub


Regards,
Stefi

„Sally†ezt írta:

 

[Guest]

Bob
Thanks for the reply and sorry for not enogh info. The file is imported so
I dont think that the what ifs will happen. This is the data layout Rows 1
& 2 are header information. A3:F3 is data as every 4th row. H3:AA6 are are
numeric entries. These are the values I am trying to total. The numbers of
rows will vary from day to day but in general H1:AAx (However many rows there
are) will be full. I need the total for each row H:AA based on the same
criteria Col G= "o" and Col F contains "F". I hope this helps and doesnt
add furhter confusion
Thanks!

 

[Guest]

The code works for H but is counting not totaling. and how do I copy it
across the other 19 cells

Thanks!