时间戳

首页 > 解决方案 > 将公式放入单元格，错误地插入值而不是公式？

vba - 将公式放入单元格，错误地插入值而不是公式？

问题描述

我在此工作簿中的所有其他工作表上都有此代码，但在 Sheet1 上，公式计算值被插入，这使得无法填写所需的公式。

Sub Change()

   Sheet1.Range("A:A").NumberFormat = "General"

   Sheet1.Range("A11:A" & SOneLastRow + 1).Clear
   SOneLastRow = Sheet1.Cells.Find("*", searchorder:=xlByRows, _
   searchdirection:=xlPrevious).Row + 1

Sheet1.Range("A11").Formula = "=IF(ISBLANK(B11),"""",1)"

Sheet1.Range("A12").Formula = "=IF(ISBLANK(B12),"""",A11+1)"

   Sheet1.Range("A12:A" & SOneLastRow).FillDown

   Sheet2.Range("A10:K" & STwoLastRow).Clear
   Sheet2.Range("A10:K10").Formula = STwoForm
   Sheet2.Range("A10:K" & SOneLastRow - 1).FillDown

   Sheet3.Range("A2:X" & SThreeLastRow).Clear
   Sheet3.Range("A2:X2").Formula = SThreeForm
   Sheet3.Range("A2:X" & SOneLastRow - 9).FillDown

   Sheet1.Range("A:A").Interior.ColorIndex = 15
End Sub

这里也是公式数组。

Sub TestForCorrect()
   SOneLastRow = Sheet1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
   STwoLastRow = Sheet2.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   SThreeLastRow = Sheet3.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   If STwoLastRow + 1 <> SOneLastRow Or SThreeLastRow <> SOneLastRow Then
    Call Change
   End If
End Sub

Sub RefreshInit()
   STwoForm(1) = "=DataRecords!G2"
   STwoForm(2) = "=DataRecords!F2"
   STwoForm(3) = "=DataRecords!K2"
   STwoForm(4) = "=DataRecords!H2"
   STwoForm(5) = "=DataRecords!I2"
   STwoForm(6) = "=DataRecords!J2"
   STwoForm(7) = "=DataRecords!L2"
   STwoForm(8) = "=CONCATENATE(DataRecords!M2, """",DataRecords!O2)"
   STwoForm(9) = "=DataRecords!N2"
   STwoForm(10) = "=DataRecords!P2"
   STwoForm(11) = "=DataRecords!R2"

   SThreeForm(1) = "=IF(DataEntry!A11="""","""",DataEntry!$H$1)"
   SThreeForm(2) = "=IF(DataEntry!A11="""","""",DataEntry!$H$3)"
   SThreeForm(3) = "=IF(DataEntry!A11="""","""",DataEntry!$H$4)"
   SThreeForm(4) = "=IF(DataEntry!A11="""","""",DataEntry!$H$6)"
   SThreeForm(5) = "=IF(DataEntry!A11="""","""",DataEntry!$H$7)"
   SThreeForm(6) = "=IF(DataEntry!A11="""","""",DataEntry!E11)"
   SThreeForm(7) = "=DataEntry!A11"
   SThreeForm(8) = "=IF(DataEntry!A11="""","""",DataEntry!B11)"
   SThreeForm(9) = "=IF(DataEntry!A11="""","""",DataEntry!C11)"
   SThreeForm(10) = "=IF(DataEntry!A11="""","""",DataEntry!D11)"
   SThreeForm(11) = "=IF(DataEntry!A11="""","""",DataEntry!F11)"
   SThreeForm(12) = "=IF(DataEntry!G11="""","""",DataEntry!G11)"
   SThreeForm(13) = "=IF(DataEntry!H11="""","""",DataEntry!H11)"
   SThreeForm(14) = "=IF(DataEntry!I11="""","""",DataEntry!I11)"
   SThreeForm(15) = "=IF(DataEntry!J11="""","""",DataEntry!J11)"
   SThreeForm(16) = "=IF(DataEntry!K11="""","""",DataEntry!K11)"
   SThreeForm(17) = "=IF(DataEntry!L11="""","""",DataEntry!L11)"
   SThreeForm(18) = "=IF(DataEntry!M11="""","""",DataEntry!M11)"
   SThreeForm(19) = "=IF(DataEntry!N11="""","""",DataEntry!N11)"
   SThreeForm(20) = "=IF(DataEntry!O11="""","""",DataEntry!O11)"
   SThreeForm(21) = "=IF(DataEntry!P11="""","""",DataEntry!P11)"
   SThreeForm(22) = "=IF(DataEntry!Q11="""","""",DataEntry!Q11)"
   SThreeForm(23) = "=IF(DataEntry!R11="""","""",DataEntry!R11)"
   SThreeForm(24) = "=IF(DataEntry!S11="""","""",DataEntry!S11)"

   Call TestForCorrect

End Sub

标签： vbaexcel

解决方案

看起来您尝试将公式写入的单元格被格式化为文本，因此它忽略了公式。在编写公式之前，您需要手动或使用 VBA将其格式更改为“常规” ：

Range("A11").NumberFormat = "General"

推荐阅读