software

Formulagt;
{=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2))-1,0)-1,0)}

What I need is a quot;Nextquot; and quot;Prevquot; Button. Right now I have quot; 1quot; button

gt; {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)-1,0)}

quot; 2quot; Button gt;
{=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 gt;

Sub PlusOne()

Range(quot;B2quot;).Select
Selection.FormulaArray = _
quot;=OFFSET(R42C1,MATCH(R2C1,LEFT(R42C1:R800C1,LEN(R2 C1)),0),0)quot;End SubA next and prev. buttomn would clean up my sheet and make using much
better.--
comotoman
------------------------------------------------------------------------
comotoman's Profile: www.excelforum.com/member.php...oamp;userid=27292
View this thread: www.excelforum.com/showthread...hreadid=503365Sub NextFormula()
Range(quot;B2quot;).FormulaArray = MakeFormula(True)
End Sub

Sub PrevFormula()
Range(quot;B2quot;).FormulaArray = MakeFormula(False)
End SubPrivate 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(quot;B2quot;)
sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56)
If Left(sSubFormula, 1) = quot;-quot; Or _
Left(sSubFormula, 1) = quot; quot; Then
iPos = InStr(1, sSubFormula, quot;,quot;)
nNext = Left(sSubFormula, iPos - 1)
nNext = IIf(AddValue, nNext 1, nNext - 1)
Else
nNext = 1
End If
sFormula = kSubFormula amp; _
IIf(nNext lt; 0, quot;quot;, quot; quot;) amp; nNext amp; _
Right(sSubFormula, Len(sSubFormula) - iPos 1)
End With
MakeFormula = sFormula
End Function

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;comotomanquot; gt; wrote
in message ...
gt;
gt; Formulagt;
gt; {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2))-1,0)-1,0)}
gt;
gt; What I need is a quot;Nextquot; and quot;Prevquot; Button. Right now I have quot; 1quot; button
gt;
gt; gt; {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0)-1,0)}
gt;
gt; quot; 2quot; Button gt;
gt; {=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A $2)),0) 1,0)}
gt;
gt; And so forth.... I am using Excel 2003
gt; Vb code for cmd buttons gt;
gt;
gt; Sub PlusOne()
gt;
gt; Range(quot;B2quot;).Select
gt; Selection.FormulaArray = _
gt; quot;=OFFSET(R42C1,MATCH(R2C1,LEFT(R42C1:R800C1,LEN(R2 C1)),0),0)quot;
gt;
gt;
gt; End Sub
gt;
gt;
gt; A next and prev. buttomn would clean up my sheet and make using much
gt; better.
gt;
gt;
gt; --
gt; comotoman
gt; ------------------------------------------------------------------------
gt; comotoman's Profile:
www.excelforum.com/member.php...oamp;userid=27292
gt; View this thread: www.excelforum.com/showthread...hreadid=503365
gt;

I pasted your code, created cmd buttons. The result I get is quot; 0,0)quot; in
b2.
Debug Linegt; sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56)
Any ideas?--
comotoman
------------------------------------------------------------------------
comotoman's Profile: www.excelforum.com/member.php...oamp;userid=27292
View this thread: www.excelforum.com/showthread...hreadid=503365You 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)

quot;comotomanquot; gt; wrote
in message ...
gt;
gt; I pasted your code, created cmd buttons. The result I get is quot; 0,0)quot; in
gt; b2.
gt; Debug Linegt; sSubFormula = Right(.FormulaR1C1, Len(.FormulaR1C1) - 56)
gt; Any ideas?
gt;
gt;
gt; --
gt; comotoman
gt; ------------------------------------------------------------------------
gt; comotoman's Profile:
www.excelforum.com/member.php...oamp;userid=27292
gt; View this thread: www.excelforum.com/showthread...hreadid=503365
gt;

B2 does contain the base formula.--
comotoman
------------------------------------------------------------------------
comotoman's Profile: www.excelforum.com/member.php...oamp;userid=27292
View this thread: www.excelforum.com/showthread...hreadid=503365