#1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default VLOOKUP

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default VLOOKUP

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



In article .com,
Nena wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default VLOOKUP

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" wrote:

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



In article .com,
Nena wrote:

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default VLOOKUP

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?


On Aug 22, 12:15 pm, JE McGimpsey wrote:
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...

In article .com,



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



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default VLOOKUP


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.


On Aug 22, 11:39 am, Nena wrote:
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?

On Aug 22, 12:15 pm, JE McGimpsey wrote:



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


In article .com,


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





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default VLOOKUP

try

IF(ISNA(VLOOKUP(LookUpValue,lookupRange,ReturnColu mn,FALSE)),0,VLOOKUP(LookUpValue,lookupRange,Retur nColumn,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" wrote:

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?


On Aug 22, 12:15 pm, JE McGimpsey wrote:
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...

In article .com,



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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If (Vlookup 0) working, but what if Vlookup cell does not exist Steve Excel Worksheet Functions 18 November 18th 09 07:33 PM
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP buffgirl71 Excel Discussion (Misc queries) 12 November 14th 06 11:36 PM
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? erikhs[_20_] Excel Programming 1 August 6th 06 06:18 PM
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) MikeR-Oz New Users to Excel 1 March 22nd 06 09:01 AM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 10:43 AM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"