VLOOKUP

N

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
 
J

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...
 
C

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

JE McGimpsey said:
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...



Nena said:
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
 
N

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...

Nena said:
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 -
 
D

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...
Nena said:
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 -
 
S

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
Nena said:
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...

Nena said:
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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP and then some 2
eliminate N/A error when using Vlookup 1
VLOOKUP 8
VLOOKUP 0
VLOOUP 1
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
VLOOKUP 4
wrong code 1

Top