VLOOKUP

N

Nena

When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?

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


Thanks in advance.
 
T

Tom Ogilvy

The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.

The problem is likely to be related to your environment when you run the
code.
 
N

Nena

Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?

The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.

The problem is likely to be related to your environment when you run the
code.

--
Regards,
Tom Ogilvy



Nena said:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -

- Show quoted text -
 
T

Tom Ogilvy

It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.

--
Regards,
Tom Ogilvy


Nena said:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?

The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.

The problem is likely to be related to your environment when you run the
code.

--
Regards,
Tom Ogilvy



Nena said:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -

- Show quoted text -
 
N

Nena

Problem solved! Thanks :)

It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.

--
Regards,
Tom Ogilvy



Nena said:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?
The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.
The problem is likely to be related to your environment when you run the
code.
--
Regards,
Tom Ogilvy
:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 
N

Nena

Tom, you've been helpful and right in the past. So I was hoping that
you could help me with this one too. The following macro doesn't seem
to be working...

Cells(6 + y, 14) = Evaluate("=sumproduct((Fall_2006_Cohort!
AB2:AB65536=1)*(Fall_2006_Cohort!N2:N65536=""A14""))")

For some reason, it won't pick up the A14 in my column but it will
pick up other text such as RVA. Is the combination between letters and
numbers? I'm not quite sure.

Thanks in advance.
It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.

--
Regards,
Tom Ogilvy



Nena said:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?
The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.
The problem is likely to be related to your environment when you run the
code.
--
Regards,
Tom Ogilvy
:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 
D

Dave Peterson

With no testing at all...

Cells(6 + y, 14) _
= Evaluate("sumproduct((Fall_2006_Cohort!AB2:AB65536=1)" _
& "*(Fall_2006_Cohort!N2:N65536=A14))")

When you use ""A14"" in your code, the formula is looking at the string "A14",
not what's in the cell A14.


Tom, you've been helpful and right in the past. So I was hoping that
you could help me with this one too. The following macro doesn't seem
to be working...

Cells(6 + y, 14) = Evaluate("=sumproduct((Fall_2006_Cohort!
AB2:AB65536=1)*(Fall_2006_Cohort!N2:N65536=""A14""))")

For some reason, it won't pick up the A14 in my column but it will
pick up other text such as RVA. Is the combination between letters and
numbers? I'm not quite sure.

Thanks in advance.
It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.

--
Regards,
Tom Ogilvy



Nena said:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?
On Aug 29, 9:52 am, Tom Ogilvy <[email protected]>
wrote:
The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.
The problem is likely to be related to your environment when you run the
code.
:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 
N

Nena

Nevermind Tom, I figured out the error.

It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.

--
Regards,
Tom Ogilvy



Nena said:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?
The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.
The problem is likely to be related to your environment when you run the
code.
--
Regards,
Tom Ogilvy
:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
 
N

Nena

Thanks.

With no testing at all...

Cells(6 + y, 14) _
= Evaluate("sumproduct((Fall_2006_Cohort!AB2:AB65536=1)" _
& "*(Fall_2006_Cohort!N2:N65536=A14))")

When you use ""A14"" in your code, the formula is looking at the string "A14",
not what's in the cell A14.




Tom, you've been helpful and right in the past. So I was hoping that
you could help me with this one too. The following macro doesn't seem
to be working...
Cells(6 + y, 14) = Evaluate("=sumproduct((Fall_2006_Cohort!
AB2:AB65536=1)*(Fall_2006_Cohort!N2:N65536=""A14""))")
For some reason, it won't pick up the A14 in my column but it will
pick up other text such as RVA. Is the combination between letters and
numbers? I'm not quite sure.
Thanks in advance.
It does that when the sheet name in the formula does not match any sheets in
the workbook. For example, when I first put tested it, I had an extra space
after the second 'Fall 2006 Cohort ' rather then 'Fall 2006 Cohort' and
got the same thing. Once I corrected the sheet name in the formula it went
away. If you believe your formula is correct, then check the actuall sheet
name and make sure it doesn't have an added space on one of the ends or
inside the name.
--
Regards,
Tom Ogilvy
:
Thanks Tom! It now runs however when the macro runs through each cell
it gives me a pop box (the one that asks if you would like to save or
open etc) and then it proceeds to the next cell. Any idea why this
might be occurring?
On Aug 29, 9:52 am, Tom Ogilvy <[email protected]>
wrote:
The code runs fine for me in xl2003. This would indicate that there is
nothing inherently wrong with your code.
The problem is likely to be related to your environment when you run the
code.
--
Regards,
Tom Ogilvy
:
When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006
Cohort'! R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -

--

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

Top