I am using EXCEL 97 and want to change a formula programmatically.

I have two strings which picks up the STARTYEAR & ENDYEAR but when I try to
add them to this formula it causes an error.

=IF(B3="H",(ROUND(VLOOKUP(E2,'AL 2005 -
2006.xls'!STAFF,(MONTH(B2)+2))*H4/40,0)),(IF(B3="D",(ROUND(VLOOKUP(E2,'AL
2005 - 2006.xls'!STAFF_DAYS,(MONTH(B2)+2))*H4/40,0)))))

Can someone help me with a solution please so that the end result is
something on the lines of:

=IF(B3="H",(ROUND(VLOOKUP(E2,'AL STARTYEAR -
ENDYEAR.xls'!STAFF,(MONTH(B2)+2))*H4/40,0)),(IF(B3="D",(ROUND(VLOOKUP(E2,'AL
STARTYEAR - ENDYEAR.xls'!STAFF_DAYS,(MONTH(B2)+2))*H4/40,0)))))

Thanks in advance.

--
Mark

Why not just put in the new formula

sub newformula
set frng =range("a2:a22")
with frng
..formula="=yournewformula"
end with
end sub
--
Don Guillett
SalesAid Software

"Mark" wrote in message
...
I am using EXCEL 97 and want to change a formula programmatically.

I have two strings which picks up the STARTYEAR & ENDYEAR but when I try
to
add them to this formula it causes an error.

=IF(B3="H",(ROUND(VLOOKUP(E2,'AL 2005 -
2006.xls'!STAFF,(MONTH(B2)+2))*H4/40,0)),(IF(B3="D",(ROUND(VLOOKUP(E2,'AL
2005 - 2006.xls'!STAFF_DAYS,(MONTH(B2)+2))*H4/40,0)))))

Can someone help me with a solution please so that the end result is
something on the lines of:

=IF(B3="H",(ROUND(VLOOKUP(E2,'AL STARTYEAR -

ENDYEAR.xls'!STAFF,(MONTH(B2)+2))*H4/40,0)),(IF(B3="D",(ROUND(VLOOKUP(E2,'AL
STARTYEAR - ENDYEAR.xls'!STAFF_DAYS,(MONTH(B2)+2))*H4/40,0)))))

Thanks in advance.

--
Mark