Select the range according to the msg of Input Box
Like Vlookup Function : just give the Range to input box From: [email protected] [mailto:[email protected]] On Behalf Of Venkat CV Sent: Wednesday, August 03, 2011 2:09 PM To: [email protected] Subject: Re: $$Excel-Macros$$ Vlookup Including Cell Comments.. Hi Rajan, Thanks for reply How can i give the inputs In Input boxes. Please explain Best Regards, Venkat Chennai On Wed, Aug 3, 2011 at 1:27 PM, Rajan_Verma <[email protected]> wrote: See if it Helps : Sub VlookupByCodes() Dim ResultRange As Range Dim SearchRange As Range Dim ColNum As Integer Dim LookUpValue As Range Dim cellResult As Range Dim CellSearch As Range Dim i As Integer Set ResultRange = Application.InputBox("Select the Range Where You want Output", , , , , , , 8) Set LookUpValue = Application.InputBox("Select the Range of searchable Value", , , , , , , 8) Set SearchRange = Application.InputBox("Select the Range to Search Value", , , , , , , 8) ColNum = Application.InputBox("Give Colnum") i = 1 For Each cellResult In ResultRange For Each CellSearch In SearchRange If CellSearch.Value = LookUpValue.Cells(i, 1).Value Then cellResult.Value = CellSearch.Offset(0, ColNum).Value If hasComment(CellSearch.Offset(0, ColNum)) = True Then cellResult.AddComment cellResult.Comment.Visible = True cellResult.Comment.Text CellSearch.Offset(0, ColNum).Comment.Text End If i = i + 1 Exit For End If Next Next Set ResultRange = Nothing Set LookUpValue = Nothing Set SearchRange = Nothing Set cellResult = Nothing Set CellSearch = Nothing End Sub Private Function hasComment(cell As Range) As Boolean On Error GoTo err: If cell.Comment.Text <> "" Then hasComment = True Else hasComment = False End If err: If err.Number <> 0 Then hasComment = False End If End Function From: [email protected] [mailto:[email protected]] On Behalf Of Venkat CV Sent: Wednesday, August 03, 2011 12:54 PM To: [email protected] Subject: Re: $$Excel-Macros$$ Vlookup Including Cell Comments.. Dear Ashish, Find attached Sample Worksheet with my requiremnt.. Best Regards, Venkat Chennai On Wed, Aug 3, 2011 at 12:01 PM, ashish koul <[email protected]> wrote: can you attach the worksheet On Wed, Aug 3, 2011 at 11:11 AM, Venkat CV <[email protected]> wrote: Dear All, Let me know how to Vlookup Including Cell Comments..in Each Cells... Best Regards, Venkat Chennai -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com <http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com <http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com <http://exceldailytip.blogspot.com/> To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul <http://akoul.blogspot.com/> akoul.blogspot.com http://akoul.posterous.com/ <http://akoul.wordpress.com/> akoul.wordpress.com My <http://in.linkedin.com/pub/ashish-koul/10/400/830> Linkedin Profile P Before printing, think about the environment. -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com <http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com <http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com <http://exceldailytip.blogspot.com/> To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
