Relykk
Please note my code has no error checking for when the number you ar
reducing is less than 1 which will cause you a calculation problem whe
you end up deviding by 0
s$ = Range("v2").FormulaR1C1
i = InStr(1, Range("v2").FormulaR1C1, "/")
s$ = Left(s, i) & Mid(s, i + 1) - 1
Range("v2").FormulaR1C1 = s
InStr = find text within text
i = InStr(1, Range("v2").FormulaR1C1, "/")
means starting at the 1st charactor search the formula in v2 for the /
the value of i = the number of characters from the left to the 1s
occurance of the searched character
s$ = Left(s, i) & Mid(s, i + 1) - 1
left(s,i)
means starting from the 1st character from the left of your text ge
the number of charactors = to the value of i
Mid(s, i + 1)
means staring in the middle of your text string at character i value
1 and all other caractors to the right of your mid string startin
point.
mid string command can also be used to specify how many charactors yo
wish to get e.g. mid("sampleText",2,5) will give you ample
Re icreasingthe 1st 2 numbers
This checks each entry in range z2 to z9 increasing the 1st number b
1
Sub UpdateDaily()
'
' UpdateDaily Macro
'
Dim i As Integer
Dim s As String
Dim c As Range
For Each c In Range("z2:z9")
s$ = Range("v2").FormulaR1C1
i = InStr(1, Range("v2").FormulaR1C1, "/")
s$ = "=" & (Mid(s, 2, i - 2) + 1) & Mid(s, i)
c.FormulaR1C1 = s
Next
End Sub
s$ = "=" & (Mid(s, 2, i - 2) + 1) & Mid(s, i)
Mid(s, 2, i - 2) means starting at the 2nd character get all the nex
charactors before the /
the reason for the i - 2 is to reduce the count for the = at the star
of the formula and for the / charator so that we have only a whol
numbe
--
mudrake
-----------------------------------------------------------------------
mudraker's Profile:
http://www.excelforum.com/member.php...nfo&userid=247
View this thread:
http://www.excelforum.com/showthread.php?threadid=27267