Use code to modify Excel formulas

Question

I have an Excel 2010 workbook with 22 worksheets in it. The first worksheet is labeled DATA, where data is entered by a user. Row A1-N1 contain labels. Rows A2-A18 contains data for January 2015 for the 17 locations beginning as cell A2, i.e. row 2 contains data for location 1, row 3 for location 3, etc. February data begins at row 19, March begins at row 36, etc.

On each worksheet A1-M1 are labels and rows 2-13 are the months January - December. Cell B2 on worksheet #2 references cell J2 on the worksheet DATA. The other formulas on row 2 of worksheet #2 reference cells on row 2 on worksheet DATA. Row 3 of worksheet #2 references cells on row 19 of DATA. In column N of each location worksheet has a number in cell N2 that corresponds to the appliable row on the worksheet DATA.

Here is an example of one of my formulas:

=(IF(ISBLANK(DATA!D2),"-",IF(ISERROR(DATA!E2/DATA!D2),"N/A",(DATA!E2/DATA!D2)))).

I would like to have VB code, or a macro?, that would edit every formula on each worksheet by replacing the current number in the formula with the number in column N of that same row, i.e. if N7 contained the number 88 the code would alter any formula on that row by removing the current number in the formula and replacing with the number 88.

Additional information:

On each worksheet there are different formulas for columns B-M. The code would need to take the number in column N for that row and replace whatever number is in that formula with the number in column N of that row, for each row, for each worksheet.

Currently, I have to touch each formula on every worksheet and this is too time consuming. Beginning in April the locations will increase from 17 to 148 which will require coding to make the appropriate changes.

DATA worksheet image

1R location worksheet image

Answer 1

You can always use something like:

If IsEmpty(Worksheets("DATA").Cells(2, 4)) And _
   IsError(Worksheets("DATA").Cells(2, 5).Value / Worksheets("DATA").Cells(2, 4).Value) Then
   ActiveCell = "NA"
Else
   ActiveCell = Worksheets("DATA").Cells(2, 5).Value / Worksheets("DATA").Cells(2, 4).Value
End If

Which is the formula you have translated to VBA code.

However, you must add error handlers, and other stuff according to what you have in your input data.

Answer 2

I am not on my work computer, so I haven't had the opportunity to test out this code, but I believe this should do what you want. If there are any issues with it, let me know, and I'll have a look at it at work tomorrow.

Sub replace_numbers()
  Dim ws As Worksheet
  Dim c As Range
  Dim replace_with As Long
  Dim objRegex As Object

  'Application.EnableEvents = False
  'Application.ScreenUpdating = False
  'Application.DisplayStatusBar = False
  'Application.Calculation = xlCalculationManual

  Set objRegex = CreateObject("vbscript.regexp")

  With objRegex
    .Global = True
    .Pattern = "\d+"
  End With

  For Each ws In Worksheets
    If ws.Name <> "DATA" Then
      For Each c In ws.Range("B2:M13")
        replace_with = CLng(Intersect(ws.Columns("N"), ws.Rows(c.Row)).Value)
        c.Formula = objRegex.Replace(c.Formula, replace_with)
      Next
    End If
  Next

  'Application.EnableEvents = True
  'Application.ScreenUpdating = True
  'Application.DisplayStatusBar = True
  'Application.Calculation = xlCalculationAutomatic
End Sub

If the code runs as intended, remove the apostrophes before the Application-calls, in order to make the code run somewhat faster.