I have an excel sheet and I am have certin bits of information looked up from
a chart. For example, I would have a chart with totals from 2006, 2007, 2008.
The following line of code selets the total form 2006. It works fine.
However, when there is no value, #n/A is displayed. I know how to tell the
indiviual cell to display somethings. When I but it into code, i receive a
syntax error and the recorder does not work with this. How should the syntax
be?


Functional in excel sheet:
=IF(ISNA(OFFSET(A28,MATCH("TOTAL",A29:A143,0),MATC H(2006,A27:S27,0)-1,1,1)),"There
is no value for this year",(OFFSET(A28,MATCH("TOTAL",
A29:A143,0),MATCH(2007,A27:S27,0)-1,1,1)))

Who can I enter this information into VBA without getting a syntax error?

Hi BZeyger,

I'm not sure how much of that code you are trying to write in VBA. Here is
how you can test a cell to see if it is an error, and if it is then I have
tested to see if it is an #N/A error:

If IsError(Cells(1, 1).Value) Then
If Cells(1, 1) = CVErr(xlErrNA) Then
MsgBox ("#N/A ERROR in cell A1")
End If
End If

Is this what you were looking for?

If you are looking to use worksheet formulae in VBA then try using
Application.WorksheetFunction and then the function:
Application.WorksheetFunction.Match(Arg1,Arg2,Arg3 )

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"BZeyger" wrote:

I have an excel sheet and I am have certin bits of information looked up from
a chart. For example, I would have a chart with totals from 2006, 2007, 2008.
The following line of code selets the total form 2006. It works fine.
However, when there is no value, #n/A is displayed. I know how to tell the
indiviual cell to display somethings. When I but it into code, i receive a
syntax error and the recorder does not work with this. How should the syntax
be?


Functional in excel sheet:
=IF(ISNA(OFFSET(A28,MATCH("TOTAL",A29:A143,0),MATC H(2006,A27:S27,0)-1,1,1)),"There
is no value for this year",(OFFSET(A28,MATCH("TOTAL",
A29:A143,0),MATCH(2007,A27:S27,0)-1,1,1)))

Who can I enter this information into VBA without getting a syntax error?