Complete formulas in VBA, rather than spreadsheets

[Godders199]

I have read that keeping formula's in VBA is alot quicker than, VBA putting the formula into a spreadsheet and the calulation being done from there. I am Keen to learn how to do this , I have a spreadsheet that takes about 2 minutes to update so i want to start looking at ways to speed this up.

I Believe, the main problem i have is the following formula, which i have currently recorded and added into the VBA to copy and paste in the spreadsheet.

=IF(SUMPRODUCT((Lists!D:D<>"")*ISNUMBER(SEARCH(Lists!D:D,'cases available '!L4))),"Mortgage","")

If anyone has examples of how i set this formula up to perform the calculations outside of the spreadsheet it be much appreciated. I only am looking for some links so i can follow examples ( appreciate this might not be the simplest formula to learn on)

 

[oldbrewer]

D:D try replacing with D1:D10000

 

[Godders199]

thanks i will try, but long term i want to advance from recording formulas, i will look searching the internet for examples of all the calulations being held in VBA

 

[oldbrewer]

vba can be used for any calculation so you need to decide which worksheet functions you need to use then search vba help

 

[Norie]

Godders199

What exactly do you mean by 'calulations being held in VBA'?