Sumproduct and Beyond
 

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

Sumproduct and Beyond
 

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)

"Sally" wrote in message
...
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

Sumproduct and Beyond
 

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:

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

Sumproduct and Beyond
 

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!

"Bob Phillips" wrote:

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)

"Sally" wrote in message
...
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

Sumproduct and Beyond
 

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

Thanks!


"Bob Phillips" wrote:

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)

"Sally" wrote in message
...
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