Vlookup result in a message box

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

I have code that returns a variable "l" to cell H17. (lowercase L)

Range("H17").Value = l

That value is used to return a vlookup value to a cell with the following formula.

=IF(ISERROR(VLOOKUP(H17,L2:Q75,J2,0)),"Game ?",VLOOKUP(H17,L2:Q75,J2,0))

I want that vlookup returned value to show in a message box.
This does not compile along with a few other tries.

MsgBox application.WorksheetFunction =IF(ISERROR(VLOOKUP(H17,L2:Q75,J2,0)),""Game ?"",VLOOKUP(H17,L2:Q75,J2,0))

Thanks.
Howard
 
I have code that returns a variable "l" to cell H17. (lowercase L)
Range("H17").Value = l

That value is used to return a vlookup value to a cell with the
following formula.

=IF(ISERROR(VLOOKUP(H17,L2:Q75,J2,0)),"Game
?",VLOOKUP(H17,L2:Q75,J2,0))

I want that vlookup returned value to show in a message box.
This does not compile along with a few other tries.

MsgBox application.WorksheetFunction
=IF(ISERROR(VLOOKUP(H17,L2:Q75,J2,0)),""Game
?"",VLOOKUP(H17,L2:Q75,J2,0))

Thanks.
Howard
Click to expand...

Pass the value of the cell containing the formula to MsgBox...

MsgBox Range("???").Value

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Pass the value of the cell containing the formula to MsgBox...



MsgBox Range("???").Value
Click to expand...

Okay, that was just a test to see if you are paying attention...<g>

Thanks,
Howard
 
Hi Howard,

Am Mon, 8 Sep 2014 13:45:54 -0700 (PDT) schrieb L. Howard:
=IF(ISERROR(VLOOKUP(H17,L2:Q75,J2,0)),"Game ?",VLOOKUP(H17,L2:Q75,J2,0))
Click to expand...

for Excel version 2007 or newer you can use:
=IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")



Regards
Claus B.
 
for Excel version 2007 or newer you can use:

=IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")
Click to expand...
Regards

Claus B.
Click to expand...

Sub Test works, but Sub TestX does not. TextX errors out on word "VLookup" for both lines.

Howard

Sub test()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rngLook As Range: Set rngLook = ws.Range("E1:F5")
Dim currName As String
Dim cellNum As Variant

currName = Range("B1")
cellNum = Application.VLookup(currName, rngLook, 2, False)
If IsError(cellNum) Then
MsgBox "Game?"
Else
MsgBox cellNum
End If
End Sub


Sub testX()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rngLook As Range: Set rngLook = ws.Range("E1:F5")
Dim currName As String

currName = Range("B1")
'IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")

'MsgBox = Application.IfError(VLookup(currName, rngLook, 2, False), "Game?")
MsgBox = IfError(VLookup(currName, rngLook, 2, False), "Game?")
End Sub
 
Howard,
You can't 'assign' a return value to the MsgBox function. I'm not sure
you can do this even for the Prompt WITHIN the function but you can
try...

MsgBox Prompt:=Application.IfError(VLookup(currName, rngLook, 2,
False), "Game?")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Howard,
You can't 'assign' a return value to the MsgBox function. I'm not
sure you can do this even for the Prompt WITHIN the function but you
can try...

MsgBox Prompt:=Application.IfError(VLookup(currName, rngLook, 2,
False), "Game?")
Click to expand...

A more conventional approach might be...

Dim sPrompt$
sPrompt = Application.IfError(VLookup(currName, rngLook, 2, False),
"Game?")

MsgBox sPrompt

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Howard,

Am Tue, 9 Sep 2014 08:14:34 -0700 (PDT) schrieb L. Howard:
'IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")
Click to expand...

if B1 is not available you get an error.
Try:

Sub testX()
Dim ws As Worksheet
Dim rngLook As Range
Dim currName As String
Dim Res As Variant

Set ws = Sheets("Sheet1")
Set rngLook = ws.Range("E1:F5")
currName = Range("B1")

With WorksheetFunction
If .CountIf(rngLook, currName) = 0 Then
Res = "Game?"
Else
Res = .VLookup("l", Range("E1:F5"), 2, False)
End If
End With

MsgBox Res
End Sub


Regards
Claus B.
 
Hi again,

Am Tue, 9 Sep 2014 08:14:34 -0700 (PDT) schrieb L. Howard:
'IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")
Click to expand...

use the find method instead:

Sub TestNew()
Dim wsh As Worksheet
Dim rngLook As Range, c As Range
Dim currName As String

Set wsh = Sheets("Sheet1")
Set rngLook = wsh.Range("E1:E5")
currName = wsh.Range("B1")

Set c = rngLook.Find(currName, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox c.Offset(, 1)
Else
MsgBox "Game?"
End If
End Sub


Regards
Claus B.
 
Back
Top