Taking a subset of a string

[Robert Chapman]

Hi,

I'm using Excel 2000 (used to using 97) and not sure how
to do something rather simple. I need to add a number to
the formula of a few cells using '& "+" & x'. However
this may be done multiple times and I don't want the
number to be added each time. Therefore I need to take
all of the formula up to the closing bracket and then add
that string on. But Excel 2000 doesn't use Find for
this! What do I use?? The code I tried is below.

TIA, Rob

Set w = Workbooks("Wastage 2003-04.xls").Worksheets
("Wastage").Cells(359, WeekNo + 3)
w.Formula = Left(w.Formula, Find(")", w.Formula)) & "+" & x

 

[Tom Ogilvy]

w.Formula = Left(w.Formula, Instr(w.Formula,")")) & "+" & x

Demo'd from the immediate window:

set w = Range("A1")
w.formula = "=Sum(B1:B10)"
? w.formula
=SUM(B1:B10)
x =10
w.Formula = Left(w.Formula, Instr(w.Formula,")")) & "+" & x
? w.formula
=SUM(B1:B10)+10
x = 5
w.Formula = Left(w.Formula, Instr(w.Formula,")")) & "+" & x
? w.formula
=SUM(B1:B10)+5

 

[Robert Chapman]

Many thanks Tom, unfortunately the VBA Help is disabled on
all our work computers so the answer was really
appreciated!

Rob