VBA中Worksheet Range函数问题：为何指定单元格区域无法匹配字符串？

Ah, I get it—your single-cell check works fine, but when you try to target a range (C2:C12), the code doesn't behave as expected. Let's break down why and fix it.

Why Your Original Code Fails #

When you write If Worksheets("Todaysbatch").Range("C2:C12") = "COOP_DAYEND" Then, VBA can't directly compare an entire range (which is an array of values) to a single string. This syntax only works for individual cells because they hold a single, discrete value—not a collection of cells.

Solution 1: Loop Through Each Cell in the Range #

The most straightforward fix is to iterate over every cell in C2:C12 and check each one individually. This gives you full control: you can run the FileCopy once when the first match is found, or repeat it for every match.

Dim cell As Range
' Loop through each cell in the target range
For Each cell In Worksheets("Todaysbatch").Range("C2:C12")
    If cell.Value = "COOP_DAYEND" Then
        ' Execute the file copy
        FileCopy COOPTEMPLATES & COOPDAYEND, newdir & COOPDAYEND
        
        ' Optional: Stop looping after the first match (remove if you want to copy for every match)
        Exit For
    End If
Next cell

Solution 2: Use CountIf for a Quick Check #

If you only need to know if any cell in the range matches "COOP_DAYEND" (and don't need to process each match individually), you can use Excel's CountIf function to streamline the code:

' Check if at least one cell in the range contains the target string
If WorksheetFunction.CountIf(Worksheets("Todaysbatch").Range("C2:C12"), "COOP_DAYEND") > 0 Then
    FileCopy COOPTEMPLATES & COOPDAYEND, newdir & COOPDAYEND
End If

Quick Notes to Avoid Other Issues #

• Double-check that variables like COOPTEMPLATES, COOPDAYEND, and newdir are correctly assigned (they should include valid file paths, e.g., C:\Templates\ instead of just Templates).
• Make sure the source file exists and you have permission to copy it to the target directory.

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