I’m having an issue with a formula that I wrote in excel to take a negative number in a column (in this case, M10), and find a positive number that can absorb it without going below 0. (ex. 10 + (5) = 5).

=ADDRESS(INDEX(M:M,MATCH(TRUE,INDEX((M:M+M10>0),),0)),13)

In the worksheet, column M contains both the positive and negative numbers, and will also be updated when the formula finds a match and combines the numbers. So for example, 10 could fit (5) twice, but for a third instance of (5), the formula would return a different value/address.

The formula above works in excel however with vba I keep getting a type mismatch error. I’ve tried storing the result in a variable and declaring it as a String, I’ve tried it without the Address portion and declaring it as Long, Integer, etc.

Application.Evaluate("ADDRESS(INDEX(M:M,MATCH(TRUE,INDEX((""M:M+"" & row1.Value & "">0""),),0)),13")

For context, row1.value is the value of the current iteration within column M, dynamically replacing M10 in the excel version.
Similar questions on SO are resolved by escaping quotations by doubling them up, which i’m already doing. Is there an issue with syntax or is there a limitation to vba for this application?

Any suggestions are much appreciated!