RE: comment enclosed using vlookup

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Jacob,

Many thx cause it works! it simplify my job...
Thx for the VBA module

Kind Regards,

"Jacob Skaria" wrote:

Hi Pran

There is no built-in functionality to do that. You can try the below UDF
(User Defined function).

Syntax:
=VLOOKUP_COMMENT(strLookupValue,rngLookUpArray,intColumn)

rngLookUpArray is the Lookuprange
strLookupValue is the lookup string or cell reference
intColumn is the column to be concatenated


Examples:
'1. To vlookup 1 in col A and return value from Column C. If comments are
present for Col C the function will return the comments to the formula cell.

=VLOOKUP_COMMENT(1,A:C,3)

OR

'with the lookup value in cell D1
=VLOOKUP_COMMENT(D1,A:C,3)


Below is the code to be pasted to the code module. From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the formula.


Function VLOOKUP_COMMENT(strLookupValue As String, _
rngLookUpArray As Range, intColumn As Integer)
Dim lngRow As Long, rngTemp As Range
For lngRow = 1 To rngLookUpArray.Rows.Count
If CStr(rngLookUpArray(lngRow, 1)) = strLookupValue Then _
VLOOKUP_COMMENT = rngLookUpArray(lngRow, intColumn): Exit For
Next

Set rngTemp = Application.Caller
If Not rngTemp.Comment Is Nothing Then rngTemp.Comment.Delete
If Not rngLookUpArray(lngRow, intColumn).Comment Is Nothing Then
rngTemp.AddComment rngLookUpArray(lngRow, intColumn).Comment.Text
End If
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Pran" wrote:

Hi,

I'm using vlookup array, which is source link has comment in every single
cell.
and I would like if comment attached from source also will be enclosed in my
summary. Is there any way to do so?

Appreciate for any help.

Pran
.

Relevant Pages

  • RE: Auto fill values with a blank row formatting between entries
    ... "Excel Autofill" wrote: ... I was having trouble saving the macro. ... "Jacob Skaria" wrote: ... Now press = and press UpArrow to the cell just above. ...
    (microsoft.public.excel.misc)
  • RE: Function needed to pull in cell values
    ... "Jacob Skaria" wrote: ... In cell G2 you can use the formula ... Column D - Misc Tools ... If myflag Then ...
    (microsoft.public.excel.programming)
  • Re: User Defined Function and VLookUP
    ... It is easy to see the formula even after applying protection as suggested ... Just copy a blank cell and then Edit> paste Special> Formats over ... , syntax wasn't working. ... "Jacob Skaria" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Filter by Colour - conditional formating
    ... Conditional formatting applied in Cell M2. ... "Jacob Skaria" wrote: ... Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean ... I have created a condition in conditional formatting highlighting a specific ...
    (microsoft.public.excel.programming)
  • RE: Text not looping through ranges
    ... There are three modules being called based on the contents of a cell. ... "Jacob Skaria" wrote: ... If any of the ranges = AH6 I want to call Special Terms Booger. ... For Each Cell In myrange2 ...
    (microsoft.public.excel.programming)