Lookup and Sum
 

I am trying to look up and sum multiple values using 2 different criteria. I
then want to put that sum in a cell on another sheet. I am currently using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like to
write VBA code to loop through this so the average user can't mess up the
formula by simply clicking on a cell! If someone could help me determine how
to do this for one column I'm sure I could apply as needed through the rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my summary
sheet and out to column FE in the formula? I just need a little help getting
started! Thanks.

Lookup and Sum
 

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different criteria.
I
then want to put that sum in a cell on another sheet. I am currently using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like to
write VBA code to loop through this so the average user can't mess up the
formula by simply clicking on a cell! If someone could help me determine
how
to do this for one column I'm sure I could apply as needed through the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.

Lookup and Sum
 

Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in "Fiormula" is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into each
cell but without the necessary = obviously, so it was all just text. Once I
corrected that mistake I began getting the object or application definition
error. Any ideas?

Thanks for your time,
Shannon

"Bob Phillips" wrote:

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different criteria.
I
then want to put that sum in a cell on another sheet. I am currently using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like to
write VBA code to loop through this so the average user can't mess up the
formula by simply clicking on a cell! If someone could help me determine
how
to do this for one column I'm sure I could apply as needed through the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.

Lookup and Sum
 

I left a $ sign in there

For i = 5 To 173
Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" &
_
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in "Fiormula"
is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into
each
cell but without the necessary = obviously, so it was all just text. Once
I
corrected that mistake I began getting the object or application
definition
error. Any ideas?

Thanks for your time,
Shannon

"Bob Phillips" wrote:

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different
criteria.
I
then want to put that sum in a cell on another sheet. I am currently
using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like
to
write VBA code to loop through this so the average user can't mess up
the
formula by simply clicking on a cell! If someone could help me
determine
how
to do this for one column I'm sure I could apply as needed through the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It
extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.

Lookup and Sum
 

Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it
setup to run through the formulas and then copy and paste values so the user
can't see the formulas. The only problem is that it takes around 10 seconds
to process all of this. Is there an easier/quicker way?

Thanks,
Shannon

"Bob Phillips" wrote:

I left a $ sign in there

For i = 5 To 173
Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" &
_
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in "Fiormula"
is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into
each
cell but without the necessary = obviously, so it was all just text. Once
I
corrected that mistake I began getting the object or application
definition
error. Any ideas?

Thanks for your time,
Shannon

"Bob Phillips" wrote:

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different
criteria.
I
then want to put that sum in a cell on another sheet. I am currently
using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like
to
write VBA code to loop through this so the average user can't mess up
the
formula by simply clicking on a cell! If someone could help me
determine
how
to do this for one column I'm sure I could apply as needed through the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It
extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.

Lookup and Sum
 

10 seconds to create the formulae, or 10 secs to recalculate.

If the former, try this


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 5 To 173
Cells(i, "G").FormulaR1C1 = _
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it
setup to run through the formulas and then copy and paste values so the
user
can't see the formulas. The only problem is that it takes around 10
seconds
to process all of this. Is there an easier/quicker way?

Thanks,
Shannon

"Bob Phillips" wrote:

I left a $ sign in there

For i = 5 To 173
Cells(i, "G").FormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" &
_
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in
"Fiormula"
is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into
each
cell but without the necessary = obviously, so it was all just text.
Once
I
corrected that mistake I began getting the object or application
definition
error. Any ideas?

Thanks for your time,
Shannon

"Bob Phillips" wrote:

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different
criteria.
I
then want to put that sum in a cell on another sheet. I am currently
using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd
like
to
write VBA code to loop through this so the average user can't mess
up
the
formula by simply clicking on a cell! If someone could help me
determine
how
to do this for one column I'm sure I could apply as needed through
the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It
extends
down to G173, but in every row the If True value changes columns.
For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.

Lookup and Sum
 

Thanks Bob. It moves a lot quicker now. I appreciate all your help.

-Shannon

"Bob Phillips" wrote:

10 seconds to create the formulae, or 10 secs to recalculate.

If the former, try this


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 5 To 173
Cells(i, "G").FormulaR1C1 = _
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shannon" wrote in message
...
Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it
setup to run through the formulas and then copy and paste values so the
user
can't see the formulas. The only problem is that it takes around 10
seconds
to process all of this. Is there an easier/quicker way?

Thanks,
Shannon

"Bob Phillips" wrote:

I left a $ sign in there

For i = 5 To 173
Cells(i, "G").FormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" &
_
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in
"Fiormula"
is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into
each
cell but without the necessary = obviously, so it was all just text.
Once
I
corrected that mistake I began getting the object or application
definition
error. Any ideas?

Thanks for your time,
Shannon

"Bob Phillips" wrote:

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Shannon" wrote in message
...
I am trying to look up and sum multiple values using 2 different
criteria.
I
then want to put that sum in a cell on another sheet. I am currently
using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd
like
to
write VBA code to loop through this so the average user can't mess
up
the
formula by simply clicking on a cell! If someone could help me
determine
how
to do this for one column I'm sure I could apply as needed through
the
rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It
extends
down to G173, but in every row the If True value changes columns.
For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my
summary
sheet and out to column FE in the formula? I just need a little help
getting
started! Thanks.