Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help, Real Easy
Formula {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2))-1,0)-1,0)} What I need is a "Next" and "Prev" Button. Right now I have "+1" button {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)-1,0)} "+2" Button {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)+1,0)} And so forth.... I am using Excel 2003 Vb code for cmd buttons Sub PlusOne() Range("B2").Select Selection.FormulaArray = _ "=OFFSET(R42C1,MATCH(R2C1,LEFT(R42C1:R800C1,LEN(R2 C1)),0),0)" End Sub A next and prev. buttomn would clean up my sheet and make using much better. -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=503365 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help, Real Easy
Sub NextFormula()
Range("B2").FormulaArray = MakeFormula(True) End Sub Sub PrevFormula() Range("B2").FormulaArray = MakeFormula(False) End Sub Private Function MakeFormula(AddValue As Boolean) As String Dim sFormula As String Dim sSubFormula As String Dim iPos As Long Dim nNext As Long With Range("B2") sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56) If Left(sSubFormula, 1) = "-" Or _ Left(sSubFormula, 1) = "+" Then iPos = InStr(1, sSubFormula, ",") nNext = Left(sSubFormula, iPos - 1) nNext = IIf(AddValue, nNext + 1, nNext - 1) Else nNext = 1 End If sFormula = kSubFormula & _ IIf(nNext < 0, "", "+") & nNext & _ Right(sSubFormula, Len(sSubFormula) - iPos + 1) End With MakeFormula = sFormula End Function -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "comotoman" wrote in message ... Formula {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2))-1,0)-1,0)} What I need is a "Next" and "Prev" Button. Right now I have "+1" button {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)-1,0)} "+2" Button {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)+1,0)} And so forth.... I am using Excel 2003 Vb code for cmd buttons Sub PlusOne() Range("B2").Select Selection.FormulaArray = _ "=OFFSET(R42C1,MATCH(R2C1,LEFT(R42C1:R800C1,LEN(R2 C1)),0),0)" End Sub A next and prev. buttomn would clean up my sheet and make using much better. -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=503365 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help, Real Easy
I pasted your code, created cmd buttons. The result I get is "+0,0)" in b2. Debug Line sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56) Any ideas? -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=503365 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help, Real Easy
You are tying the buttons to the NextFormula and PrevFormula macros? And B2
should start with your base formula. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "comotoman" wrote in message ... I pasted your code, created cmd buttons. The result I get is "+0,0)" in b2. Debug Line sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56) Any ideas? -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=503365 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help, Real Easy
B2 does contain the base formula. -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=503365 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for sorting different rows | Excel Worksheet Functions |