Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO assistance | Excel Discussion (Misc queries) | |||
Macro assistance | Excel Worksheet Functions | |||
Incrementing formula by two rows | Excel Worksheet Functions | |||
incrementing formula reference by 7 | Excel Discussion (Misc queries) | |||
Macro Assistance with Incrementing Formula | Excel Programming |