excel如何将公式通过宏写入单元格中并生效

Question

Sub 搜索()
Application.ScreenUpdating = 0
Range("c2:k65536").ClearContents
On Error Resume Next
For Each ch In Sheets
r = ""
a = [c65536].End(xlUp).Row + 1
If UCase(ch.Name) = "SHEET1" Then GoTo 1
r = Sheets(ch.Name).Cells.Find(What:=[b2]).Address
fr = r
If r = "" Then GoTo 2
Cells(a, 3) = ch.Name
Cells(a, 4) = Sheets(ch.Name).Range(r)
Cells(a, 5) = Sheets(ch.Name).Cells(3, Sheets(ch.Name).Range(r).Column).Value
Cells(a, 6) = "=ISNUMBER(--LEFT(D2,3))"
Cells(a, 7) = "=LEFT(D2,MIN(IF(LENB(MID(D2,ROW($1:$100),1))=2,ROW($1:$100)))-1)"
Cells(a, 8) = "=SUBSTITUTE(D2,G2,"")"
2: r = Sheets(ch.Name).Cells.FindNext(after:=Sheets(ch.Name).Range(r)).Address
If r = fr Then GoTo 1
a = [c65536].End(xlUp).Row + 1
Cells(a, 3) = ch.Name
Cells(a, 4) = Sheets(ch.Name).Range(r)
Cells(a, 5) = Sheets(ch.Name).Cells(3, Sheets(ch.Name).Range(r).Column).Value
Cells(a, 6) = "=ISNUMBER(--LEFT(D2,3))"
Cells(a, 7) = "=LEFT(D2,MIN(IF(LENB(MID(D2,ROW($1:$100),1))=2,ROW($1:$100)))-1)"
Cells(a, 8) = "=SUBSTITUTE(D2,G2,"")"
GoTo 2
1: Next
Application.ScreenUpdating = 1
End Sub
上面的宏公式的那段是不能生效的。

原来=ISNUMBER(--LEFT(D2,3))
是复制后下拉到这一列其他单元格实现的，下一个就是=ISNUMBER(--LEFT(D3,3))............
Cells(a, 7) = "=LEFT(D2,MIN(IF(LENB(MID(D2,ROW($1:$100),1))=2,ROW($1:$100)))-1)"
Cells(a, 8) = "=SUBSTITUTE(D2,G2,"")"
是通过“CTRL+SHIFT+回车”后下拉这一列其他单元格单元格实现的。

现在我想通过宏直接将公式写入单元格的，应该怎么实现呢？
我测试了下,Cells(a, 7) .FormulaArray= "=LEFT(D2,MIN(IF(LENB(MID(D2,ROW($1:$100),1))=2,ROW($1:$100)))-1)"是可以使用的，但是Cells(a,7).FormulaArray="=LEFT(RC[-3],MIN(IF(LENB(MID(RC[-3],ROW($1:$100),1))=2,ROW($1:$100)))-1)"就不行。
不知道Cells(a, 8) = "=SUBSTITUTE(D2,G2,"")"应该如何修改，

Answer 1

用宏写通用公式

，有一个办法就是改成R1C1模式，这时的公式通用性极强！

Cells(a, 6) = "=ISNUMBER(--LEFT(RC[-2],3))"

“CTRL+SHIFT+回车”的公式是数组公式，在宏里要这样写

Cells(a, 7) .FormulaArray= "=LEFT(D2,MIN(IF(LENB(MID(D2,ROW($1:$100),1))=2,ROW($1:$100)))-1)"

追问

Cells(a, 6) = "=ISNUMBER(--LEFT(RC[-2],3))"可以使用，木有问题！
Cells(a,7).FormulaArray="=LEFT(RC[-3],MIN(IF(LENB(MID(RC[-3],ROW($1:$100),1))=2,ROW($1:$100)))-1)"
使用后没有效果。
相同的,Cells(a,8）是怎样的呢？
追加20分数，期待您的答案！

追答

Cells(a, 8).FormulaArray = "=SUBSTITUTE(RC[-4],RC[-1],"""")"

Answer 2

我没用过“CTRL+SHIFT+回车”的公式，但是你用录制宏记录以下，就知道应该是什么代码了