VLOOKUP

[Nena]

I would like to remove the N/A's and replace them with a 0 but none
of
these macros seem to be working properly. Two problems that I
encountered is that is (1) the macro does run but by replacing all
the
cells with a 0 (2) it does not stay within the data range, so it runs
through all the cells.

ActiveCell.FormulaR1C1 = "=IF(COUNTIF(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,)) VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)0))"
ActiveCell.Offset(0, 1).Select


Active.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)),0,VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select

 

[JE McGimpsey]

Without seeing more of your macro, one can't really even hazard a guess,
since neither of your statements are syntactically correct or will
(directly) cause either of the problems you specify...

 

[carloss]

Use this formula

If(iserror(vlookup(a,b,c,d)),0,vlookup(a,b,c,d))

The "a,b,c,d" represents the parameters in your lookup

Without seeing more of your macro, one can't really even hazard a guess,
since neither of your statements are syntactically correct or will
(directly) cause either of the problems you specify...

I would like to remove the N/A's and replace them with a 0 but none
of
these macros seem to be working properly. Two problems that I
encountered is that is (1) the macro does run but by replacing all
the
cells with a 0 (2) it does not stay within the data range, so it runs
through all the cells.

ActiveCell.FormulaR1C1 = "=IF(COUNTIF(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,)) VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)0))"
ActiveCell.Offset(0, 1).Select


Active.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)),0,VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select

 

[Nena]

Okay then what could I do to fix it? I simply want to replace the N/
A's with a 0 in it's place.

What would that macro look like?

Without seeing more of your macro, one can't really even hazard a guess,
since neither of your statements are syntactically correct or will
(directly) cause either of the problems you specify...

I would like to remove the N/A's and replace them with a 0 but none
of
these macros seem to be working properly. Two problems that I
encountered is that is (1) the macro does run but by replacing all
the
cells with a 0 (2) it does not stay within the data range, so it runs
through all the cells.

ActiveCell.FormulaR1C1 = "=IF(COUNTIF(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,)) VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)0))"
ActiveCell.Offset(0, 1).Select

Active.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)),0,VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select- Hide quoted text -

- Show quoted text -

 

[Don]

Selection.SpecialCells(xlCellTypeFormulas, 16).Value = 0

or instead of Selection.SpecialCells, you can use the actual
Range("??:??").SpecialCells so you never actually have to select the
cells.

Okay then what could I do to fix it? I simply want to replace the N/
A's with a 0 in it's place.

What would that macro look like?

Without seeing more of your macro, one can't really even hazard a guess,
since neither of your statements are syntactically correct or will
(directly) cause either of the problems you specify...

I would like to remove the N/A's and replace them with a 0 but none
of
these macros seem to be working properly. Two problems that I
encountered is that is (1) the macro does run but by replacing all
the
cells with a 0 (2) it does not stay within the data range, so it runs
through all the cells.
ActiveCell.FormulaR1C1 = "=IF(COUNTIF(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,)) VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)0))"
ActiveCell.Offset(0, 1).Select
Active.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)),0,VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[SysAccountant]

try

IF(ISNA(VLOOKUP(LookUpValue,lookupRange,ReturnColumn,FALSE)),0,VLOOKUP(LookUpValue,lookupRange,ReturnColumn,FALSE))

It looks specifically for N/A ,and if true returns "0",else if performs an
additional VLookup

It works for me.

HTH

SysAccountant

Okay then what could I do to fix it? I simply want to replace the N/
A's with a 0 in it's place.

What would that macro look like?

Without seeing more of your macro, one can't really even hazard a guess,
since neither of your statements are syntactically correct or will
(directly) cause either of the problems you specify...

I would like to remove the N/A's and replace them with a 0 but none
of
these macros seem to be working properly. Two problems that I
encountered is that is (1) the macro does run but by replacing all
the
cells with a 0 (2) it does not stay within the data range, so it runs
through all the cells.

ActiveCell.FormulaR1C1 = "=IF(COUNTIF(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,)) VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)0))"
ActiveCell.Offset(0, 1).Select

Active.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)),0,VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select- Hide quoted text -

- Show quoted text -