| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to get the VLookup property of the WorksheetFunction class
How do I go around this runtime error? I tried the following:
If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction class
this issue is that VLOOKUP and other similar function raise an error when no
match is found. Wrap it in an error trap - personally, I do this within a user defined function so that my error handling doesn't break, but not everybody likes this ... ON ERROR RESUME NEXT result = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) ON ERROR GOTO 0 "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction class
Another way..
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else 'whatever End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction class
Probably related to the inputs you are supplying and how they are Dim'ed:
Sub servient() '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''' Dim c As Range, rngsStatusdata As Range Set c = Range("A1") c.Offset(o, 2).Value = 2 Set rngsStatusdata = Range("A1:Z100") '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then MsgBox ("true") Else MsgBox ("false") End If End Sub works just fine. -- Gary''s Student - gsnu200905 "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 "Jacob Skaria" wrote: Another way.. If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else 'whatever End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
this is my extire code:
Private Sub cmdUpdate_Click() Dim insiteWS As Worksheet, trueOnAirWS As Worksheet, sStatusWS As Worksheet Dim rngInsitedata As Range, rngsStatusdata As Range, c As Range Dim reportCurrentRow As Integer Dim insitelastRow As Integer, trueOnAirlastRow As Integer, sStatuslastRow As Integer Application.Calculation = xlCalculationManual Set sStatusWS = Worksheets("Starting Status 9-29-2009") sStatuslastRow = sStatusWS.Range("A65536").End(xlUp).Row Set rngsStatusdata = sStatusWS.Range("$C$2:$I$" & sStatuslastRow & "") Set insiteWS = Worksheets("InSite Milestones") insitelastRow = insiteWS.Range("A65536").End(xlUp).Row Set rngInsitedata = insiteWS.Range("A2:A" & insitelastRow & "") Set trueOnAirWS = Worksheets("True On Air Status") trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row trueOnAirWS.Range("A2:I" & trueOnAirlastRow).ClearContents reportCurrentRow = 2 For Each c In rngInsitedata.Cells If c.Offset(0, 5) = "Actual" And c.Offset(0, 7).Value < "Actual" Then trueOnAirWS.Range("A" & reportCurrentRow & "") = c.Value trueOnAirWS.Range("B" & reportCurrentRow & "") = c.Offset(0, 1).Value trueOnAirWS.Range("C" & reportCurrentRow & "") = c.Offset(0, 2).Value trueOnAirWS.Range("D" & reportCurrentRow & "") = c.Offset(0, 3).Value trueOnAirWS.Range("E" & reportCurrentRow & "") = c.Offset(0, 4).Value trueOnAirWS.Range("F" & reportCurrentRow & "") = c.Offset(0, 5).Value trueOnAirWS.Range("G" & reportCurrentRow & "") = c.Offset(0, 6).Value trueOnAirWS.Range("H" & reportCurrentRow & "") = c.Offset(0, 7).Value If WorksheetFunction.CountIf(Column1, c.Offset(0, 2).Value) 0 Then trueOnAirWS.Range("J" & reportCurrentRow & "") = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If reportCurrentRow = reportCurrentRow + 1 End If Next c trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row For Each c In trueOnAirWS.Range("J2:J" & trueOnAirlastRow).Cells If c.Value = "AFTER 9/30" Then c.Offset(0, -1) = c.Value End If Next c Application.Calculation = xlCalculationAutomatic Exit Sub End Sub "Gary''s Student" wrote: Probably related to the inputs you are supplying and how they are Dim'ed: Sub servient() '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''' Dim c As Range, rngsStatusdata As Range Set c = Range("A1") c.Offset(o, 2).Value = 2 Set rngsStatusdata = Range("A1:Z100") '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then MsgBox ("true") Else MsgBox ("false") End If End Sub works just fine. -- Gary''s Student - gsnu200905 "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
Column1 should be a range object referring to the 1st column of the range
rngsStatusdata. If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: I get and "Object required" error when I used If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 "Jacob Skaria" wrote: Another way.. If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else 'whatever End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
Didn't work. still getting the error. This is the entire subroutine:
Private Sub cmdUpdate_Click() Dim insiteWS As Worksheet, trueOnAirWS As Worksheet, sStatusWS As Worksheet Dim rngInsitedata As Range, rngsStatusdata As Range, c As Range Dim reportCurrentRow As Integer Dim insitelastRow As Integer, trueOnAirlastRow As Integer, sStatuslastRow As Integer Application.Calculation = xlCalculationManual Set sStatusWS = Worksheets("Starting Status 9-29-2009") sStatuslastRow = sStatusWS.Range("A65536").End(xlUp).Row Set rngsStatusdata = sStatusWS.Range("$C$2:$I$" & sStatuslastRow & "") Set insiteWS = Worksheets("InSite Milestones") insitelastRow = insiteWS.Range("A65536").End(xlUp).Row Set rngInsitedata = insiteWS.Range("A2:A" & insitelastRow & "") Set trueOnAirWS = Worksheets("True On Air Status") trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row trueOnAirWS.Range("A2:I" & trueOnAirlastRow).ClearContents reportCurrentRow = 2 For Each c In rngInsitedata.Cells If c.Offset(0, 5) = "Actual" And c.Offset(0, 7).Value < "Actual" Then trueOnAirWS.Range("A" & reportCurrentRow & "") = c.Value trueOnAirWS.Range("B" & reportCurrentRow & "") = c.Offset(0, 1).Value trueOnAirWS.Range("C" & reportCurrentRow & "") = c.Offset(0, 2).Value trueOnAirWS.Range("D" & reportCurrentRow & "") = c.Offset(0, 3).Value trueOnAirWS.Range("E" & reportCurrentRow & "") = c.Offset(0, 4).Value trueOnAirWS.Range("F" & reportCurrentRow & "") = c.Offset(0, 5).Value trueOnAirWS.Range("G" & reportCurrentRow & "") = c.Offset(0, 6).Value trueOnAirWS.Range("H" & reportCurrentRow & "") = c.Offset(0, 7).Value 'If WorksheetFunction.CountIf(Column1, c.Offset(0, 2).Value) 0 Then On Error Resume Next trueOnAirWS.Range("J" & reportCurrentRow & "") = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) 'End If On Error GoTo 0 reportCurrentRow = reportCurrentRow + 1 End If Next c trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row For Each c In trueOnAirWS.Range("J2:J" & trueOnAirlastRow).Cells If c.Value = "AFTER 9/30" Then c.Offset(0, -1) = c.Value End If Next c Application.Calculation = xlCalculationAutomatic End Sub "Patrick Molloy" wrote: this issue is that VLOOKUP and other similar function raise an error when no match is found. Wrap it in an error trap - personally, I do this within a user defined function so that my error handling doesn't break, but not everybody likes this ... ON ERROR RESUME NEXT result = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) ON ERROR GOTO 0 "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
Dim rngColumn1 as Range
Set rngColumn1 = sStatusWS.Range("C2:C" & sStatuslastRow) If WorksheetFunction.CountIf(rngColumn1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else Msgbox "Not found" End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: I get and "Object required" error when I used If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 "Jacob Skaria" wrote: Another way.. If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else 'whatever End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction class
Do you really want to do any comparisons to " "? Your =vlookup() could return a
single space character? ======== I'd use: dim Res as variant 'could be an error res = application.vlookup(location, sheets("City").range("a2:b3", 2, false) with Worksheets("Master") if iserror(res) then .range("V3").value = "Missing" else .range("V3").value = res end if end with ========== Saved from a previous post: There is a difference in the way application.vlookup() and worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave. Application.vlookup returns an error that you can check: dim Res as variant 'could return an error res = application.vlookup(....) if iserror(res) then msgbox "no match" else msgbox res end if Application.worksheetfunction.vlookup raises a trappable error that you have to catch: dim res as variant on error resume next res = application.worksheetfunction.vlookup(...) if err.number < 0 then msgbox "no match" else msgbox res end if on error goto 0 (application.match() and application.worksheetfunction.match() behave the same way.) Personally, I find using the application.vlookup() syntax easier to read. But it's personal preference. Ayo wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction cl
Thanks Jacob. Works perfectly.
"Jacob Skaria" wrote: Dim rngColumn1 as Range Set rngColumn1 = sStatusWS.Range("C2:C" & sStatuslastRow) If WorksheetFunction.CountIf(rngColumn1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else Msgbox "Not found" End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: I get and "Object required" error when I used If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 "Jacob Skaria" wrote: Another way.. If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, 0) Else 'whatever End If If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: How do I go around this runtime error? I tried the following: If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < " " Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) < "#N/A" Then trueOnAirWS.Range("J" & reportCurrentRow & "") = Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False) End If |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unable to get the VLookup property of the WorksheetFunction class
the best advice is, don't use worksheet functions in VBA.
I see you are using Offset, but why use vlookup at all then? Use ONLY offset. It's actually more useful and easier to use than vlookup anyway. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Unable to get the VLookup property of the WorksheetFunction class | Excel Discussion (Misc queries) | |||
| unable to get match property of WorksheetFunction class | Excel Programming | |||
| Unable to get Match property of the WorksheetFunction class | Excel Programming | |||
| Unable to get the Vlookup property of the WorksheetFunction class | Excel Programming | |||
| Unable to get the Vlookup Property of the WorkSheetFunction Class | Excel Programming | |||
Linear Mode
