Taking a subset of a string
 

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

Taking a subset of a string
 

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

--
Regards,
Tom Ogilvy

"Robert Chapman" wrote in message
...
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

Taking a subset of a string
 

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

Rob

-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Robert Chapman"
wrote in message
...
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



.