Macro Assistance with Incrementing Formula
 

Thanks guys. I tried all of your suggestions, and mudraker's was th
only one I was able to get to work without an error.

My knowledge of VB is basic, but I'm able to make sense of most of it
However, there are certain functions which confuse me, such as th
InStr with Left and Right.

I have one other question, which I tried figuring out myself, bu
didn't work.

If I want to change mudraker's code to increment the following...

Cells Z:2 through Z:9 have percentages within them, all the same, bein
22.58%, in the worksheet. I have to increment the formula: =14/62 by 1
but changing the 14 to a 15, so that the formula will become =15/62
changing the result to 24.19%, how do I do that now?

I tried playing around with the code, but messed up several ways, wit
some being that the result was in decimal form instead of percentage
and then got all 0's another way.


Thank you again in advance

--
Relyk
-----------------------------------------------------------------------
Relykk's Profile: http://www.excelforum.com/member.php...fo&userid=1575
View this thread: http://www.excelforum.com/showthread.php?threadid=27267

Macro Assistance with Incrementing Formula
 

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