A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VLOOKUP in VBA



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 12:58 PM posted to microsoft.public.excel.worksheet.functions
G.P.N.L. c.v.a.
external usenet poster
 
Posts: 13
Default VLOOKUP in VBA

I am a beginner in VBA; nevertheless, I already have several working UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _
Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?



  #2  
Old April 21st, 2010, 01:12 PM posted to microsoft.public.excel.worksheet.functions
Niek Otten
external usenet poster
 
Posts: 2,533
Default VLOOKUP in VBA

Declare ABC as Double instead of Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"G.P.N.L. c.v.a." wrote in message
...
I am a beginner in VBA; nevertheless, I already have several working UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _
Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?




  #3  
Old April 22nd, 2010, 07:51 AM posted to microsoft.public.excel.worksheet.functions
G.P.N.L. c.v.a.
external usenet poster
 
Posts: 13
Default VLOOKUP in VBA

Sorry, Niek, doesn't make any difference
Gilbert

"Niek Otten" wrote in message
...
Declare ABC as Double instead of Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"G.P.N.L. c.v.a." wrote in message
...
I am a beginner in VBA; nevertheless, I already have several working UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _

Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")), _
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?






  #4  
Old April 22nd, 2010, 01:47 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default VLOOKUP in VBA

How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on error" code.

======
Ps. This assumes that that other workbook is open, has a sheet named Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!



"G.P.N.L. c.v.a." wrote:

I am a beginner in VBA; nevertheless, I already have several working UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _
Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?


--

Dave Peterson
  #5  
Old April 23rd, 2010, 09:54 PM posted to microsoft.public.excel.worksheet.functions
G.P.N.L. c.v.a.
external usenet poster
 
Posts: 13
Default VLOOKUP in VBA

Thanks, Dave...but, indeed, it says "Not found", but that does of course not
help me.
What can cause this error ?
BTW, just to test, I changed "...(ABC As Date)" into "...(ABC As Variant)",
and then the result is "0" (?). Does that help ?
Sorry for the trouble,
Regards,
Gilbert

"Dave Peterson" wrote in message
...
How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on error"
code.

======
Ps. This assumes that that other workbook is open, has a sheet named
Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's
next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!



"G.P.N.L. c.v.a." wrote:

I am a beginner in VBA; nevertheless, I already have several working
UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _

Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?


--

Dave Peterson



  #6  
Old April 23rd, 2010, 10:20 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default VLOOKUP in VBA

How about:

res = Application.VLookup(cLng(ABC), myRng, 2, True)

You can still pass the ABC variable as a date.



"G.P.N.L. c.v.a." wrote:

Thanks, Dave...but, indeed, it says "Not found", but that does of course not
help me.
What can cause this error ?
BTW, just to test, I changed "...(ABC As Date)" into "...(ABC As Variant)",
and then the result is "0" (?). Does that help ?
Sorry for the trouble,
Regards,
Gilbert

"Dave Peterson" wrote in message
...
How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on error"
code.

======
Ps. This assumes that that other workbook is open, has a sheet named
Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's
next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!



"G.P.N.L. c.v.a." wrote:

I am a beginner in VBA; nevertheless, I already have several working
UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _

Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?


--

Dave Peterson


--

Dave Peterson
  #7  
Old April 24th, 2010, 10:10 PM posted to microsoft.public.excel.worksheet.functions
G.P.N.L. c.v.a.
external usenet poster
 
Posts: 13
Default VLOOKUP in VBA

Works !
Thank you, David !
(But why ?)

"Dave Peterson" wrote in message
...
How about:

res = Application.VLookup(cLng(ABC), myRng, 2, True)

You can still pass the ABC variable as a date.



"G.P.N.L. c.v.a." wrote:

Thanks, Dave...but, indeed, it says "Not found", but that does of course
not
help me.
What can cause this error ?
BTW, just to test, I changed "...(ABC As Date)" into "...(ABC As
Variant)",
and then the result is "0" (?). Does that help ?
Sorry for the trouble,
Regards,
Gilbert

"Dave Peterson" wrote in message
...
How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng =
Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on
error"
code.

======
Ps. This assumes that that other workbook is open, has a sheet named
Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng =
Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table
is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's
next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!



"G.P.N.L. c.v.a." wrote:

I am a beginner in VBA; nevertheless, I already have several working
UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _

Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?

--

Dave Peterson


--

Dave Peterson



  #8  
Old April 24th, 2010, 11:55 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default VLOOKUP in VBA

Dates are misterious beasts!

I don't know why VBA has as much trouble as it does with them.

"G.P.N.L. c.v.a." wrote:

Works !
Thank you, David !
(But why ?)

"Dave Peterson" wrote in message
...
How about:

res = Application.VLookup(cLng(ABC), myRng, 2, True)

You can still pass the ABC variable as a date.



"G.P.N.L. c.v.a." wrote:

Thanks, Dave...but, indeed, it says "Not found", but that does of course
not
help me.
What can cause this error ?
BTW, just to test, I changed "...(ABC As Date)" into "...(ABC As
Variant)",
and then the result is "0" (?). Does that help ?
Sorry for the trouble,
Regards,
Gilbert

"Dave Peterson" wrote in message
...
How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng =
Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on
error"
code.

======
Ps. This assumes that that other workbook is open, has a sheet named
Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng =
Workbooks("INDEX.XLS").Worksheets("INDEX").Range(" DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table
is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's
next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!



"G.P.N.L. c.v.a." wrote:

I am a beginner in VBA; nevertheless, I already have several working
UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _

Range(Workbooks("INDEX.XLS").Worksheets("INDEX").R ange("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is
A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:15 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.