Excel VBA code required (for.......next loop)

[uplink600]

Hi

Can anyone help with some code for a macro I need. I have
enclosed a .xls file which explains what I need to achieve. I need
a for........next loop to loop down a column looking for certai
products and then giving a sum of the values in the next column fo
each particular item, but only if there is no entry in the third colum
(a date value) for each row.

The enclosed file explains this better and I hope someone can advis
me, The file is macro & virus free.

Thank

 

[Frank Kabel]

Hi
without looking at your file (as I don't see it) do you
require a macro or would a worksheet function work for you
also. If yes you may use SUMPRODUCT. e.g.
=SUMPRODUCT(--(A1:A1000="product_name1"),--
(C1:C1000=""),B1:B1000)

 

[uplink600]

Thanks

I will try this. If it won't work can I email the .xls file to you for
clarification.

VC

 

[uplink600]

Thanks

This did work on the current list in the worksheet. I adapted th
formula t
=SUMPRODUCT(--(Orders!$D$8:$D$100="83201220"),--(Orders!$K$8:$K$100=""),Orders!$G$8:$G$100).........thi
works
fine as the product 83201220 was already in the list on the Order
sheet.

When I added product 83201020 to the list on the Orders sheet and use
the formul
=SUMPRODUCT(--(Orders!$D$8:$D$100="83201020"),--(Orders!$K$8:$K$100=""),Orders!$G$8:$G$100).....thi
failed to work.

Have you any idea why this might be.

Thanks

V

 

[Frank Kabel]

Hi
does
=SUMPRODUCT(--(Orders!$D$8:$D$100=83201020),--(Orders!$K$8:$K$100=""),O
rders!$G$8:$G$100)
work?
If no what is your exact problem with the existing formula?

 

[uplink600]

Thank you

This is strange. When I set up the formula on the worksheet I had
some products in the range D828 and the formula worked fine
with the product code in speech marks ie "83201220".

When I added items to cells D9 onwards it didn't work. I change th
formula to your suggestion ie without speech marks and it work
fine????

Do you know why although I'm happy it works and thanks for your help.

V