在VBA宏中使用R1C1表示法调用单元格属性时出现对象定义错误

Hey there, I see exactly what's causing that object defined error in your code! Let's break it down and fix it step by step.

The Root Problem #

When you write RC[lastcolumnbc] inside the FormulaR1C1 string, Excel doesn't recognize lastcolumnbc as your VBA variable—it treats it as literal text in the formula. That's why you're getting an error, because Excel can't resolve that "lastcolumnbc" reference in the worksheet context.

The Fix: Insert the Variable Value into the Formula String #

You need to concatenate the actual value of lastcolumnbc into your formula string using VBA's string concatenation operator (&). Here's how to adjust your code:

Set rgbe = .Range(.Cells(1, 2), .Cells(lastrow - 1, 2))
' No need to select the range—we can set the formula directly
rgbe.FormulaR1C1 = "=INDEX(RC[1]:RC[" & lastcolumnbc & "],MATCH(TRUE,INDEX((RC[1]:RC[" & lastcolumnbc & "]<>0),0),0))"
rgbe.Columns.AutoFit

Why This Works #

By using "RC[" & lastcolumnbc & "]", we're replacing the variable name with its actual numeric value (the column number you stored earlier) before passing the string to Excel as a formula. Excel now sees a valid R1C1 reference like RC[5] instead of the invalid RC[lastcolumnbc].

Bonus: Avoid Using .Select #

I also removed the .Select and Selection. calls because relying on selection in VBA is slow, prone to errors, and unnecessary. You can directly modify the rgbe range's properties without selecting it first—this is a best practice in VBA coding.

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