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

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

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

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

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