| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
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 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 "Nena" wrote: 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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
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" wrote: 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 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 "Nena" wrote: 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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Problem solved! Thanks :)
On Aug 29, 11:16 am, Tom Ogilvy wrote: 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" wrote: 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 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 "Nena" wrote: 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 - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
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. On Aug 29, 11:16 am, Tom Ogilvy wrote: 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" wrote: 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 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 "Nena" wrote: 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 - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
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. Nena wrote: 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. On Aug 29, 11:16 am, Tom Ogilvy wrote: 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" wrote: 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 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 "Nena" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Nevermind Tom, I figured out the error.
On Aug 29, 11:16 am, Tom Ogilvy wrote: 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" wrote: 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 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 "Nena" wrote: 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 - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Thanks.
On Sep 6, 1:53 pm, Dave Peterson wrote: 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. Nena wrote: 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. On Aug 29, 11:16 am, Tom Ogilvy wrote: 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" wrote: 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 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 "Nena" wrote: 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
![Nena[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
