Vlookup formula - works with F9 - does not work with simple 'calculate' Macro

[Jamie McMillan]

Hello,

I have a simple vlookup formula, =IF(C13=0,"",VLOOKUP(C13,$A$13:$B$27,2))

It works properly when I press F9 (Excel is on manual calculation)

I use two very simple macros to calculate the sheet: then it does not work.

The first macro 'EveryOne' is attached to a shape (Labelled 'Any' in the attached image - the result is also on the image.)

(It sometimes works; but then, maybe 1 it 10 tries it fails.)????

Sub EveryOne()
    Range("A3").Value = 3
    Call HowLong
End Sub

Sub HowLong()
    Range("B1").Value = 0
    Calculate
End Sub

Crazy!!!

Thanks for anything. (I have closed it and reopened the file, and restarted Excel.)

Jamie

 

[Jamie McMillan]

Hello,

I solved it.

If it happens to anyone else.

The number that was being looked up was not 14. it was 14.00000000000000000001

It was subtracting two Int(values) ; so should always have been an integer.

I simply rounded it, now it always works. =ROUND(D7,1)

Thanks to anyone who thought about it.

Jamie