VLOOKUP and then some

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

This one doesn't seem to want to run at all...

TotStudentstotcrn2006a("C:C").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Range("A6"), _
Unique:=True

Any suggestions?

Thanks in advance!
 
D

Dave Peterson

That first formula looks funny to me, especially the ,FALSE)0))" portion.

And maybe...
TotStudentstotcrn2006a("C:C").AdvancedFilter
should be:
TotStudentstotcrn2006a.range("C:C").AdvancedFilter

But that's a guess.

I'd record a macro when I did it manually and look at the code.


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

This one doesn't seem to want to run at all...

TotStudentstotcrn2006a("C:C").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Range("A6"), _
Unique:=True

Any suggestions?

Thanks in advance!
 
N

Nena

THANKS!


That first formula looks funny to me, especially the ,FALSE)0))" portion.

And maybe...
TotStudentstotcrn2006a("C:C").AdvancedFilter
should be:
TotStudentstotcrn2006a.range("C:C").AdvancedFilter

But that's a guess.

I'd record a macro when I did it manually and look at the code.




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
This one doesn't seem to want to run at all...
TotStudentstotcrn2006a("C:C").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Range("A6"), _
Unique:=True
Any suggestions?
Thanks in advance!

--

Dave Peterson- 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


Top