help to get output from message to the current cell

[ChachiAZ]

I need some help. I have "zero" VB skills. I got this function from
Microsoft's Web site ...and it works perfectly with the exception it
outputs to a MsgBox. I need the output to go to a specific cell.
Actually, I need it to output to the active cell I called the function
in. Currently only get the message then the cell fills with 0. Any
ideas?

Function test(x)
MsgBox Application. _
VLookup(x, Workbooks("test_of_vlookup.xls"). _
Worksheets("Sheet2").Range("C10:E25"), 3, False)
End Function

I attached a workbook. If you click on Sheet1!D25 you can see the
MsgBox in action.


+----------------------------------------------------------------+
| Attachment filename: test_of_vlookup.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=349894|
+----------------------------------------------------------------+

 

[jawilbu]

Have you tried getting the help of your cube mates. I hear they are
pretty smart.

 

[James S]

Hi ChachiAZ,

I added a little bit to your code. Try this:

Function Test(x)
Dim strValue As String
strValue = Application. _
VLookup(x, Workbooks("test_of_vlookup.xls"). _
Worksheets("Sheet2").Range("C10:E25"), 3, False)
MsgBox strValue
ActiveCell = strValue
End Function

It will display a message box with the value, then put
that value into the active cell. Hope that helps.

Regards,
James S

-----Original Message-----

I need some help. I have "zero" VB skills. I got this function from
Microsoft's Web site ...and it works perfectly with the exception it
outputs to a MsgBox. I need the output to go to a specific cell.
Actually, I need it to output to the active cell I called the function
in. Currently only get the message then the cell fills with 0. Any
ideas?

Function test(x)
MsgBox Application. _
VLookup(x, Workbooks("test_of_vlookup.xls"). _
Worksheets("Sheet2").Range("C10:E25"), 3, False)
End Function

I attached a workbook. If you click on Sheet1!D25 you can see the
MsgBox in action.


+------------------------------------------------------ ----------+
| Attachment filename:

test_of_vlookup.xls |

|Download attachment: http://www.excelforum.com/attachment.php?postid=349894|
----------+

http://www.ExcelForum.com/

 

[ChachiAZ]

James,
Thanks for the help. I tried to use the code you gave me, but it still
isn't working. I tried the following...

Range("H15").Value = strValue

...inplace of of your ...

ActiveCell = strValue

...code, but it doesn't work either. Does anybody know if there is a
way to get the strValue to output to any cell?

david

 

[Jake Marx]

Hi david,

Since you are using the function as a UDF (User-Defined Function) in a
worksheet, you must set the return value to what you want the result of the
formula to be. In your case, your function would be:

Function test(x)
test = Application.WorksheetFunction.VLookup(x, _
Worksheets("Sheet2").Range("C10:E25"), 3, False)
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dave Peterson]

Is there a reason you're not using it as a worksheet function. One of the
problems with this function is if you change something in C10:e25 of that table
workbook.

The function doesn't know you're using that range. So it won't recalculate.

I'd use the native excel function with in the worksheet, but if you don't want
to, then add:

application.volatile

at the top of your subroutine.

(Hit F9 before you trust your output to force a calculation.)