Excel Macro Help?

f1charlie

Great! I'm glad my (very) little bit of knowledge came in useful!

f1charlie

Put in the cell where you are calculating the average:

=IF(ISERROR(AVERAGE(B20,C20,D20)),"",AVERAGE(B20,C20,D20))

replacing the cells with the ones you want, of course!

Aboswell

Use the =if(iserror(... function so for the cell under 2 where the 1st div/o appears type this......

where 01.09.07 is in cell D1 and 02.09.07 is in cell E1 and so forth:-

=if(iserror(average(D3:G3)),"",(average(D3:G3)))

substitute the "" with a 0 if thats what you want it to read as. or "xx" where XX = whatever txt you want to see.

This basically says IF the formula you are using returns an error put the "" alternitively show the result of the formula if it returns a value. I assume that the formula you have used is =average(D3:G3)?

hope this helps fella

Aboswell

By the way i can think of several better macros for your find function, including the find in range function which would probably be best. also there are some functions that can find parts of an input in a given range that might help you allow for more flexibity in the input.

Unfortunatley i live in the VBA matrix all day long automating stuff. Strangley i quite enjoy it, i think i may be a little sick, but it keeps me in beer tokens and off the street!

Pm me if you need any more help

Regards
Adam

robert_harper_2000

nice one guys worked a treat! Tell me more about these search functions sounds brilliant!

Cheers,

rmg1

I have a couple of books I can send to you that deal with VBA programming for MS Excel.
PM me you're e-mail address if you want copies.

isofa

I'd highly recommend "Excel 2000 Power Programming" by John Walkenbach no doubt it's been updated for newer versions, but it is one of the best Excel/VBA books I've used. I've written some complex systems in Excel, and it's a great reference to have beside you.

robert_harper_2000

Bought it! Thanksisofa

robert_harper_2000

Plenty to read from your link! blimey.

While I sort through that does anyone fancy continuing helping me??

Next problem is I have £0.00 values - perhaps I need the =if(iserror( formula?