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
Relykk wrote:
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
--
Relykk
------------------------------------------------------------------------
Relykk's Profile: http://www.excelforum.com/member.php...o&userid=15758
View this thread: http://www.excelforum.com/showthread...hreadid=272679
--
Dave Peterson