CODEWITHSUNDEEP
excelexcel-formulavba
Nicholas
Nicholas

Reputation: 1

Excel VBA - Add one to each referenced cell when running macro

I have tried searching for the answer for the past couple hours but cannot seem to find what I am looking for - mind you, I am really new to VBA and macros.

What I am trying to do is to add one to what my formula is referencing to every time the macro is run. For example:

Currently the formula is =100*((Q$179/Q$167)-1)

When run, I would like it to be =100*((Q$180/Q$168)-1)

I have to do this for multiple formulas. Is there an easy way to do this with a macro?

Thank you so much in advance

Upvotes: 0

Views: 345

Answers (3)

Tom
Tom

Reputation: 9878

Personally I'd use RegEx to achieve this. The following will increment every cells row by 1 in your formula. This should also work on columns other then Q as well

Option Explicit
Sub IncrementFormula()
    Dim tmpFormula As String
    Dim RegEx As Object
    Dim Match, SubMatch

    tmpFormula = "=100*((Q$179/Q$167)-1)"

    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "(?:[A-Z]{1,})(?:\$|)([0-9]{1,})"

        If .test(tmpFormula) Then
            For Each Match In .Execute(tmpFormula)
                For Each SubMatch In Match.submatches
                    tmpFormula = Replace(tmpFormula, Match, Replace(Match, SubMatch, vbNullString) & CLng(SubMatch) + 1)
                Next SubMatch
            Next Match
        End If
    End With
    MsgBox tmpFormula
End Sub

Using your formula above it will output =100*((Q$180/Q$168)-1)

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

A different approach is to use OFFSET in your formula

Assuming the formula is in Q185

then:

=100*((Q$180/Q$168)-1)

Becomes:

 =100*((OFFSET(Q185,-5,0)/OFFSET(Q185,-17,0)-1))

As you insert rows at the bottom of the table (and above the formulas), the formula and the cell it refers to will also move down

Upvotes: 0

Cullen
Cullen

Reputation: 54

If you want it to persist when the workbook is closed, you'll need to store your number somewhere in a worksheet. Say it's in Cell(1,1) of Sheets(2), you could use

    dim incVal as integer
    incVal = cint(Sheets(2).Cells(1,1))+1
    Sheets(2).Cells(1,1) = incVal
    dim formula as string
    formula = "=100*((Q$" & incVal & "/Q$" & (incVal-12) & ")-1)"

Then just set the desired Cell's formula to that formula. Hope this helps.

Upvotes: 0

Related Questions