Macro Help, Real Easy

C

comotoman

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" butto
{=OFFSET($A$42,MATCH($A$2,LEFT($A$42:$A$800,LEN($A$2)),0)-1,0)}
Click to expand...

"+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(R2C1)),0),0)"


End Sub


A next and prev. buttomn would clean up my sheet and make using muc
better
 
B

Bob Phillips

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)
 
C

comotoman

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?
 
B

Bob Phillips

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)
 
Top