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

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,int Column)

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

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,int Column)

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