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

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



--

Garry


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

Thanks,
Howard

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



MsgBox Range("???").Value



--

Garry


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

Thanks,
Howard

Ha, ha, ha, ha, ha...

--
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 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))

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



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

for Excel version 2007 or newer you can use:

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

Regards

Claus B.


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?")

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?")

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Tue, 9 Sep 2014 08:14:34 -0700 (PDT) schrieb L. Howard:

'IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks, guys.

Some good examples for me to refer to.

Howard