VLOOKUP

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

 

[Guest]

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.

 

[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

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 -

 

[Guest]

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?

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 -

 

[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

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 -

 

[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

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 -

 

[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

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 -

 

[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

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 -

 

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