CODEWITHSUNDEEP
vbaexcel
Frits Verstraten
Frits Verstraten

Reputation: 2189

VBA cant read values produced by a formula

I have calculated values in cell ranges G8:G12 by the following formula (I defined the cell properties as number)

=RIGHT(A8,LEN(A8)-FIND(" ",A8)) 
=RIGHT(A9,LEN(A9)-FIND(" ",A10)) 
=RIGHT(A9,LEN(A9)-FIND(" ",A10)) 
...

Now, using VBA, I would like to extract the max values from this range. Therefore I do:

Function end_calculation()

 max_value = WorksheetFunction.Max(Sheets("screen_3_FIXEDPRICE_NSCLIENT").Range("G8:G12"))
 MsgBox (max_value)

End sub

This however gives me "0". This is strange cause when I just type "hard" values in the range A8:A12 it does work... Any thoughts what goes wrong here and what I should do?

Upvotes: 0

Views: 758

Answers (1)

Cyriaque
Cyriaque

Reputation: 26

This is because you have to add in your formulas the Value excel formula.

Try to replace your cell formulas by :

 =Value(RIGHT(A8,LEN(A8)-FIND(" ",A8)))
 =Value(RIGHT(A9,LEN(A9)-FIND(" ",A10)))
 =Value(RIGHT(A9,LEN(A9)-FIND(" ",A10)))

Tell me if that works.

Upvotes: 1

Related Questions