if you want to use formula directly in vba with out putting your figures in any cell then do like this.
suppose you want minimum value of range cell "A1 To A10" in a variable
then write your code:-
minv= WorksheetFunction.Min(range("a1:a10"))
suppose you want minimum value of range cell "A1 To A10" in a variable
then write your code:-
minv= WorksheetFunction.Min(range("a1:a10"))
but if the formlae contains reference of differnt sheet than it doesnt seem to work
ReplyDeleteEg
a = Application.WorksheetFunction.IF(MAX(OFFSET(Table!R1C486,MATCH('Crane Main Sheet'!RC[-1],Table!R2C484:R176C484,0),0,VLOOKUP(RC[-1],Table!C[406]:C[414],9,0),1))<=MAX(RC[-7],RC[-5]),""Value exeded max av"",VLOOKUP(VLOOKUP(MAX(RC[-7],RC[-5]),OFFSET(Table!R1C486,MATCH('Crane Main Sheet'!RC[-1],Table!R2C484:R176C484,0),0,VLOOKUP(RC[-1],Table!C[406]:C[414],9,0),10),8,1)+1,Table!C[405]:C[415],8,0))