Macro Assistance with Incrementing Formula

  • Thread starter Thread starter Relykk
  • Start date Start date
R

Relykk

I have an Excel sheet that I use to keep track of salespeople's dail
statistics, which has manual changes made to it daily. These change
are made to the formula resident within the cell by either incrementin
or decrementing the last number in the formula.
The below code is from merely recording a new macro, selecting the to
data cell in the column, and then clicking within the fx textbox
revealing the formula...

Code
-------------------

Sub UpdateDaily()
'
' UpdateDaily Macro
'
Range("V2").Select
ActiveCell.FormulaR1C1 = "=(165-RC[-20])/48"

End Sub

-------------------


What I want is for the formula: =(165-B2)/48 to be decreased by 1 eac
time the macro is run, so that it will now be...=(165-B2)/47, and the
=(165-B2)/46...each time I run the macro.

If anyone can help me with this, it will be deeply appreciated.

Thank yo
 
How about:

Option Explicit
Sub UpdateDaily2()

Dim myFormula As String
Dim myValue As String
Dim iCtr As Long

With Worksheets("sheet1")
myFormula = .Range("V2").FormulaR1C1
For iCtr = Len(myFormula) To 1 Step -1
If Mid(myFormula, iCtr, 1) = "/" Then
'found the /
myValue = Mid(myFormula, iCtr + 1)
If IsNumeric(myValue) Then
If myValue <> 1 Then
myFormula = Mid(myFormula, 1, iCtr) & CDbl(myValue) - 1
.Range("v2").FormulaR1C1 = myFormula
Exit For
Else
MsgBox "division by 0???"
End If
End If
End If
Next iCtr
End With

End Sub

I have an Excel sheet that I use to keep track of salespeople's daily
statistics, which has manual changes made to it daily. These changes
are made to the formula resident within the cell by either incrementing
or decrementing the last number in the formula.
The below code is from merely recording a new macro, selecting the top
data cell in the column, and then clicking within the fx textbox,
revealing the formula...

Code:
--------------------

Sub UpdateDaily()
'
' UpdateDaily Macro
'
Range("V2").Select
ActiveCell.FormulaR1C1 = "=(165-RC[-20])/48"

End Sub

--------------------

What I want is for the formula: =(165-B2)/48 to be decreased by 1 each
time the macro is run, so that it will now be...=(165-B2)/47, and then
=(165-B2)/46...each time I run the macro.

If anyone can help me with this, it will be deeply appreciated.

Thank you
Click to expand...
 
Relykk


TRY


Sub UpdateDaily()
'
' UpdateDaily Macro
'
Dim i As Integer
Dim s As String

s$ = Range("v2").FormulaR1C1
i = InStr(1, Range("v2").FormulaR1C1, "/")
s$ = Left(s, i) & Mid(s, i + 1) - 1
Range("v2").FormulaR1C1 = s
End Sub
 
If your formula is entered in cell Z10, the following code will decrease the
final number by one every time it is run.
Sub Test()
strFormula = Cells(10, 26).FormulaR1C1
intSlash = InStr(1, strFormula, "/")
If InStr(1, strFormula, "/") > 0 Then
LastNumber = Mid(strFormula, intSlash + 1, Len(strFormula) -
intSlash)
strFormula = Left(strFormula, Len(strFormula) - Len(LastNumber)) _
& Trim(Str(Val(LastNumber) - 1))
Cells(10, 26).FormulaR1C1 = strFormula
End If
End Sub

HTH
Chris
 
Back
Top