0

I am using the following VBA code to add a formula to a cell:

   Dim Rng As Range
   Dim cel As Range
   Dim i As Integer
   Dim AmountOfCells As Integer
   Dim varray As Variant
   Dim varrayValid As Variant
   Dim pctCompl1 As Single
   Dim pctCompl2 As Single
   Dim ref As Integer
   
   
   ' UPDATE COLUMN A
   
   Set Rng = Range("A12:A1521")
   ref = 9
    For Each cel In Rng.Cells
            With cel
                .Formula = "='Weekly Results'!E" & ref
                ref = ref + 1
            End With
            
    Next cel

'Weekly Results' refers to a valid sheet.

However, my formula is not being evaluated as a formula. When I manually select a cell, and from the menu bar choose 'Evaluate Formula' I get a message telling me that "the current cell being evaluated contains a constant".

I have set the following in code Application.Calculation = xlAutomatic, and in Options\Formulas\Workbook Calculation is set to Automatic.

If I Find and Replace '= with =, then the formula will evaluate correctly.

Can anyone point me in the correct direction?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
LMORSE
  • 35
  • 2
  • 6
  • Codes are fine and working for me. Did you declared variables correctly? Post you full code. – Harun24hr Sep 08 '21 at 06:34
  • I have updated the post with a bit more of my code showing defs. – LMORSE Sep 08 '21 at 06:52
  • i have tried your code in my system it works fine. try to clear the range before you apply the formula. Use **Rng.Clear** after **Set Rng = Range("A12:A1521")** if you don't have any formating issue involve. – Mukibul Hasan Sep 08 '21 at 07:19
  • 2
    Remove the "Text" formatting from the column A. Set to "General". – GSerg Sep 08 '21 at 07:25

1 Answers1

0

Thank you mishu36, I added 'Rng.Clear' and it seems to have worked.

LMORSE
  • 35
  • 2
  • 6