Help with code

Printable View

07-04-2011, 08:17 AM
jsfon

Help with code

Hello all,

Considering the following:

[vba]
Sheet 1 (dynamic named range for different fruits)

ColumnA | ColumnB | ColumnC
Banana
Apple
...

Sheet 2 (montly evaluation by different persons and on different criteria)

ColumnA(fruit) | ColumnB(month) | ColumnC(year) | ColumnD(taste) | ColumnE(colour)
banana | june | 2011 | good | poor
banana | june | 2011 | poor | good
Apple | june | 2011 | avarage | poor
banana | july | 2011 | good | average

[/vba]
If I define in sheet3 the month and year for my report ( A1=June and A2=2011), the macro should display in sheet 1 the average score for each fruit in the different criteria (columnB = ctaste, columnC = colour) based on: "poor"=1, "average"=2 and "good"=3.

So in this example (june 2011) the result would be:
[vba]
Sheet 1 (dynamic named range for different fruits that is expandeble as new fruits are added from time to time)

ColumnA | ColumnB | ColumnC
Banana 2 2
Apple 2 1
cheries 'empty 'empty
...
[/vba]

Can you please help me with a VBAcode for this?
07-04-2011, 11:30 AM
Bob Phillips

Post a workbook, your example is confusing.
07-05-2011, 01:39 AM
jsfon

1 Attachment(s)

Workbook included.
07-05-2011, 02:39 AM
Bob Phillips

No need for VBA< use this array formula

=SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A2),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average") *2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))
07-05-2011, 04:51 AM
jsfon

Hi xld, thank you for your help.

The result I have with the formula is 1 but it should be 1,5. Actually it always gives 1 divided by 1.

There is also another point, the fruit list will be increased (dynamic named range), how can I have a macro to insert the formula in each column when a new fruit is added. (the best option would be a code to add the formula to each row when the report is pulled and also adapt the reference to the fruit name accordigly to each entry in the list: A1,A2,A3 and so on as long as the named range).
07-05-2011, 04:54 AM
jsfon

Sorry, my bad. Just realised it was an array formula. It gives the correct result when entered with shift+ctrl+enter.

Can you please help on the second point above? and how do I enter a array formula in the cell in code?

Thanks.
07-05-2011, 05:07 AM
Bob Phillips

You use the FormulaArray property.
07-05-2011, 06:16 AM
jsfon

Many thanks. Your help was presious.

Can you also help me with the rest of the code please?

Something like this:
For each row(i) in named range Fruitlist
Column 2 FormulaArray = the formula you provided but the ref A2 is replaced by the "A" & row number (i).
07-07-2011, 06:12 AM
jsfon

Can someone help with the last part of the code please?

What I what is to insert an array formula in column B for each row of a named range but the array formula needs to be adapted to reference the fist cell of that specific row.

So in B2 it should insert the array: =SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A2),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average") *2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))

And in cell B3 the array should be: =SUM(IF((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*(Sh eet2!$C$1:$C$20=$A3),(Sheet2!D$1:D$20="Poor")*1+(Sheet2!D$1:D$20="Average") *2+(Sheet2!D$1:D$20="Good")*3))/
MAX(1,SUM((Sheet2!$B$1:$B$20=Sheet3!$A$3)*(Sheet2!$A$1:$A$20=Sheet3!$A$2)*( Sheet2!$C$1:$C$20=$A2)))

and so long as long as the named range. I need to do it this way because this is a dynamic range that grows over time.

Thanks