Putting formulas into cells,erroneously inserts value instead of formula?

0
votes

I have this code working on all the other sheets in this workbook, but on Sheet1 the formulas calculated value is insterted instead, which makes it impossible to filldown with the needed formula.

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

Here's the formula arrays as well.

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
2
vbaexcel

2 Answers

0
votes

Looks like the cell you are trying to write formula into is formatted as text, so it ignores the formula. You need to change its format to "General" before writing the formula, either manually or with VBA:

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

I used a Boolean public variable to postpone the onchange routine until my other routines are done.

Sub Change()
  WaitOnChange = True

   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").Value = "=IF(ISBLANK(B11),"""",1)"
   Sheet1.Range("A12").Value = "=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

  WaitOnChange = False

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Module1.WaitOnChange = True Then Exit Sub
...
...