Search in cell and comments at the same time

[shaju]

Is it possible through VB to check for a specific value in a cell AND comment with one macro.
Something like Range(“A1:H50”). Find=“010118”, LookIn=xlComments/Values ...!!!
TIA

 

[Rick Rothstein]

Exactly how you intend to use what you asked for is not entirely clear. Also you did not say if the value you are looking for needs to be the only text in the cell or in the comment (I assumed other text could be in the cell and in the comment along with the text you were searching for). You also did not say if the search needed to be case sensitive or not (I assumed not). Given that, maybe this function which can be used either in other VBA code or as a UDF (user defined function) will work for you. The function's first argument is the value you want to find and the second argument is the range you want to search. Finally, the function returns the address of the cell where both the value and comment contain the searched for text.

Function ValComment(S As String, Rng As Range) As String
  Dim Cell As Range
  For Each Cell In Rng
    If InStr(1, Cell.Value, S, vbTextCompare) Then
      If InStr(1, Cell.Comment.Text, S, vbTextCompare) Then
        ValComment = Cell.Address(0, 0)
        Exit Function
      End If
    End If
  Next
End Function

 

[shaju]

Thank you Rick the function. How can it be used as a macro? Or is it to be used as a function?

I have dates in DD-MM-YY format in Column C, and the same date comes in comment boxes in range (F3:AD”XX”), or (F3:AD”XX”) is a dynamic range. I just want to get the cells with specific date, AND cells with comment box having the specific date. Or in other words, instead of searching for a specific date with FIND AND THEN searching for the same date in comments , I want to run JUST ONE macro which will list all the required addresses.