Excel VBA issue - XLookup #Value error

Question

I'm having issues with a VBA function and can't understand why. I've tracked the error to a specific point as shown below. When I run this funciton using this code, it works exactly as expected.

However, When I run this code, which should do exactly the same thing, It gives me a #VALUE! error as shown below.

Could somebody please advise how to fix this? The rest of the function runs as intended and will work once this returs the correct value and not an error.

Thanks

Answer 1

Maybe you should use a range:

Dim arg3 as Range
Set arg3 = Range("D9")

Or maybe

Dim arg3
arg3 = Range("D9").Value2