Application.Evaluate使用带>=条件的SUMIF时失效问题求助

Let's break down why your previous attempts weren't working, then fix the code properly:

What Went Wrong #

1. First Attempt:
   Your initial code didn't wrap the condition in quotes. Excel's SUMIF expects a text-based criteria like ">=5", but without quotes, Application.Evaluate treats >=J1 as a logical test that returns True/False—which isn't a valid criteria for summing values, hence the boolean result.

2. Second Attempt:
   The quote syntax was misaligned. Your code generated a criteria string like ">="J1 (no proper connection between the operator and value), which Excel can't parse as a valid condition. That's why it returned 0 even when matching values existed.

Correct Solution #

You need to build a fully valid Excel formula string where the criteria is properly quoted and formatted. Here are two reliable ways to do it with Application.Evaluate:

Option 1: Double-Quote Escaping (VBA Standard)

MsgBox Application.Evaluate("=SUMIF(E1:E15,"">=" & Range("J1").Value & """,F1:F15)")

• In VBA, we use two double-quotes ("") to represent a single double-quote in the final formula string. This builds a criteria like ">=7" (if J1 holds 7) that SUMIF can interpret correctly.

Option 2: Using Chr(34) for Readability

If nested double-quotes feel confusing, use Chr(34) (the ASCII code for a double-quote) to construct the criteria:

MsgBox Application.Evaluate("=SUMIF(E1:E15," & Chr(34) & ">=" & Range("J1").Value & Chr(34) & ",F1:F15)")

This produces the exact same valid formula as Option 1, but many developers find this syntax easier to read and debug.

Key Takeaway #

Application.Evaluate requires a syntactically perfect Excel formula as a string. For SUMIF criteria with operators, you must wrap the entire operator+value combo in quotes within that string—getting VBA's string escaping rules right is the critical piece here.

内容的提问来源于stack exchange，提问作者x0nar