0

I am getting a 1004 Error when I run the below code. Sometimes it works but many times it breaks on the ActiveCell.Formula Line. I can't understand why it does and does not work sometimes.

I am looking to input "$" into the existing formulas for a bunch of cells.

Code:

Dim Orig_Formula As String

Orig_Formula = ActiveCell.Formula

Orig_Formula = Mid(Orig_Formula, 1, 31) & "$" & Mid(Orig_Formula, 32, 1) & "$" & Mid(Orig_Formula, 33)

ActiveCell.Formula = Orig_Formula

ActiveCell.offset(1, 0).Activate

Many Thanks

BigBen
  • 46,229
  • 7
  • 24
  • 40
GnawEilay
  • 1
  • 3
    You may be reinventing the wheel: https://stackoverflow.com/questions/38821608/change-cell-reference-between-absolute-and-relative – BigBen Jan 08 '21 at 19:03
  • `Debug.Print Orig_Formula` then try pasting it into a cell - what error do you get? – Tim Williams Jan 08 '21 at 19:03
  • No Error, In the immediate window, the String is concatenated correctly - "=IF(ISNUMBER(Sheet3!B4),Sheet3!$A$4,0)" I'll look into the convert formula function, thank you. – GnawEilay Jan 08 '21 at 19:12
  • `Orig_Formula = ActiveCell.Formula` What is the original formula? – Siddharth Rout Jan 09 '21 at 06:53
  • The Formula stored in the cells are VLookups. "=Vlookup(B2, Sheet!1 Etc..)" I realized that this is not the correct approach however since I am referencing the target $ locations absolutely while it should be relatively referenced. The local variable should still be recognized by ActiveCell.Formula though I believe. – GnawEilay Jan 10 '21 at 15:17

0 Answers0