help to get output from message to the current cell
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| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
help to get output from message to the current cell
Have you tried getting the help of your cube mates. I hear they are pretty smart. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
help to get output from message to the current cell
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| +------------------------------------------------------ ----------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
help to get output from message to the current cell
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
help to get output from message to the current cell
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] ChachiAZ wrote: 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
help to get output from message to the current cell
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.) ChachiAZ wrote: 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| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ -- Dave Peterson |
All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com