| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and VBA
I thought I posted this, but I can't find it now so it's possible it never
was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
Barb,
It is likely that your range name as entered in code does not match your actual named range's name: because of where it broke in the message, I suspect that you have a space in the name in your code, which is not allowed. Otherwise, the code should work fine. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
Hi Barb,
Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
I'm not sure what I need. That's why I'm asking. Defining what I'd call a
constant based on data in the workbook is new to me. "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
You don't need the .worksheetfunction in your code.
Can you get a formula similar to this working in excel? =vlookup("something",Statics_Query_from_MS_Access_ Database, 2,false) (I like Bernie's suggestion.) Barb Reinhardt wrote: I'm not sure what I need. That's why I'm asking. Defining what I'd call a constant based on data in the workbook is new to me. "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
I figured out what I needed.
CompanyID = Application.WorksheetFunction.VLookup(Target, Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
Glad you're sorted. Apologies for the red herring!
Have a good weekend John "Barb Reinhardt" wrote in message ... I figured out what I needed. CompanyID = Application.WorksheetFunction.VLookup(Target, Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
Hi Dave,
I'm sure you're right, but what's the reason for .worksheetfunction not being needed? Is it a default somehow? Best regards John "Dave Peterson" wrote in message ... You don't need the .worksheetfunction in your code. Can you get a formula similar to this working in excel? =vlookup("something",Statics_Query_from_MS_Access_ Database, 2,false) (I like Bernie's suggestion.) Barb Reinhardt wrote: I'm not sure what I need. That's why I'm asking. Defining what I'd call a constant based on data in the workbook is new to me. "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
I needed to make a change so as to use a different target. This is what I have:
CompanyID = Application.WorksheetFunction.VLookup("R" & Target.Row & "C" & (Target.Column + 1), Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) basically, I want to do a lookup of the data that is one column to the right of the cell that is changed. I think my problem is that my first lookup argument is the location of the value to lookup, not what's being checked (R3C5, not "Company1"). I'd put in indirect, but this won't work. Suggestions? Barb Reinhardt Thanks, Barb "John" wrote: Glad you're sorted. Apologies for the red herring! Have a good weekend John "Barb Reinhardt" wrote in message ... I figured out what I needed. CompanyID = Application.WorksheetFunction.VLookup(Target, Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
..worksheetfunction syntax was added in xl97 (IIRC).
But Excel/VBA still supports lots of stuff from xl95 and before. But there is a difference in the way they behave. application.vlookup() will return an error that you can check: dim myVal as Variant 'could be an error myval = application.vlookup(....) if iserror(myval) then 'same as #n/a else 'found it end if On the other hand, application.worksheetfunction.vlookup() will cause a trappable error. Dim myVal as Variant 'or string or long or double ... on error resume next myval = application.worksheetfunction.vlookup(...) if error.number < 0 then 'same as #n/a else 'found it end if on error goto 0 ======== I find the application.vlookup() easier to use. John wrote: Hi Dave, I'm sure you're right, but what's the reason for .worksheetfunction not being needed? Is it a default somehow? Best regards John "Dave Peterson" wrote in message ... You don't need the .worksheetfunction in your code. Can you get a formula similar to this working in excel? =vlookup("something",Statics_Query_from_MS_Access_ Database, 2,false) (I like Bernie's suggestion.) Barb Reinhardt wrote: I'm not sure what I need. That's why I'm asking. Defining what I'd call a constant based on data in the workbook is new to me. "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
I'd use:
Dim CompanyID as Variant 'could be error companyid = application.vlookup(target.offset(0,+1).value, _ Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, _ False) if iserror(companyid) then 'not found else 'go it end if I dropped the .worksheetfunction portion. Barb Reinhardt wrote: I needed to make a change so as to use a different target. This is what I have: CompanyID = Application.WorksheetFunction.VLookup("R" & Target.Row & "C" & (Target.Column + 1), Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) basically, I want to do a lookup of the data that is one column to the right of the cell that is changed. I think my problem is that my first lookup argument is the location of the value to lookup, not what's being checked (R3C5, not "Company1"). I'd put in indirect, but this won't work. Suggestions? Barb Reinhardt Thanks, Barb "John" wrote: Glad you're sorted. Apologies for the red herring! Have a good weekend John "Barb Reinhardt" wrote in message ... I figured out what I needed. CompanyID = Application.WorksheetFunction.VLookup(Target, Sheets("Statics").Range("Query_from_MS_Access_Data base"), 2, False) "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP and VBA
Perfect explanation. Thanks for your time Dave.
Best regards John "Dave Peterson" wrote in message ... .worksheetfunction syntax was added in xl97 (IIRC). But Excel/VBA still supports lots of stuff from xl95 and before. But there is a difference in the way they behave. application.vlookup() will return an error that you can check: dim myVal as Variant 'could be an error myval = application.vlookup(....) if iserror(myval) then 'same as #n/a else 'found it end if On the other hand, application.worksheetfunction.vlookup() will cause a trappable error. Dim myVal as Variant 'or string or long or double ... on error resume next myval = application.worksheetfunction.vlookup(...) if error.number < 0 then 'same as #n/a else 'found it end if on error goto 0 ======== I find the application.vlookup() easier to use. John wrote: Hi Dave, I'm sure you're right, but what's the reason for .worksheetfunction not being needed? Is it a default somehow? Best regards John "Dave Peterson" wrote in message ... You don't need the .worksheetfunction in your code. Can you get a formula similar to this working in excel? =vlookup("something",Statics_Query_from_MS_Access_ Database, 2,false) (I like Bernie's suggestion.) Barb Reinhardt wrote: I'm not sure what I need. That's why I'm asking. Defining what I'd call a constant based on data in the workbook is new to me. "John" wrote: Hi Barb, Do you need the WorksheetFunction object in there? ie "Application.WorksheetFunction.VLookup(......" Best regards John "Barb Reinhardt" wrote in message ... I thought I posted this, but I can't find it now so it's possible it never was posted. If it's a duplicate, my apologies. I have the following equation that's not working and I'm not sure why CompanyID = Application.VLookup(Target, Range("Statics Query_from_MS_Access_Database"), 2, False) I get runtime error 1004. Method 'range' of object '_worksheet' failed I have a named range of Query ... on the Statics worksheet. Thanks, Barb Reinhardt -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
