Auto comment based on stored values

[deltas247]

Hi all, new to this great resource.

I am trying to figure out how to do the following:

On worksheet "S" I have a range of n x m dimension that is dynamic, and I want to test the string value (alphanumeric) of any given cell on worksheet "S" against data I have on a separate worksheet "R" of a 2 column dynamic range(B & C columns to n rows). Specifically, to test the value of said string on "S" of any cell against all values stored in column B. Once a match is found, a comment is added to the tested cell and the corresponding alphabetical string in column C of "S" is then inserted into the comment body.

In summary:
Worksheet "R" contains:
Column B alphanumeric dynamic list of n rows
Column C alphabetic dynamic list of n rows that corresponds to Column B in that n is equal and each row of values is linked

Worksheet "S" contains:
Dynamic range of n x m dimension whose cell values correspond to Worksheet "R" Column B alphanumeric values exclusively

Example: Worksheet "S" cells D23, E11, and AS6 contain the string "XJ6502"
Worksheet "R" columns B-C has element B6 containing the string "XJ6502" and C6 contains the string "Program AECSG"

Upon clicking commandbutton VBA matches the contents of D23, E11, and AS6 on "S" with B6 on "R" and creates a comment in D23, E11, and AS6 on "S", then inserts the value of C6 on "R" into the newly created comment windows of D23, E11, and AS6.

This program should disregard blank cells and if possible, should not input the user information upon comment creation. I am new to posting so I tried to be very detailed, not sure if this is common practice but the forum rules did not specify brevity. I would greatly appreciate any input on how to go about doing this.

 

[sijpie]

Hi Deltas247, welcome to the board!
An interesting question of yours. And you explained it very well! I wish more people would do it that way. Give me a little bit time to get back.

 

[sijpie]

Hi Deltas247, this code should do it. Copy it into a module. Then run it from the spreadsheet by pressing the F8 kkey and selecting the macro to run.

If you have problems, let me know.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> SearchS()<br>    <SPAN style="color:#00007F">Dim</SPAN> vIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, vChk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lChk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, UBIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, UBChk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rCl <SPAN style="color:#00007F">As</SPAN> Range, rIn <SPAN style="color:#00007F">As</SPAN> Range, rChk <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#007F00">'///>>> This macro assumes there may be empty cells _<br>     /// but no empty rows or empty columns in the range _<br>     /// to be checked in sheet S. Also that the range _<br>     /// starts in A1. if not change the start cell below.</SPAN><br>     <br>    <SPAN style="color:#00007F">Set</SPAN> rIn = Sheets("S").Range("A1")<br>    <SPAN style="color:#00007F">Set</SPAN> rChk = Sheets("R").Range("B1")<br>     <br>    <SPAN style="color:#007F00">'load the two ranges in an array in memory for fast processing</SPAN><br>    vIn = rIn.CurrentRegion.Value<br>    vChk = rChk.CurrentRegion.Value<br>    <br>    <SPAN style="color:#007F00">'get the nr of rows of vChk</SPAN><br>    UBChk = <SPAN style="color:#00007F">UBound</SPAN>(vChk, 1)<br>    <br>    <SPAN style="color:#007F00">'now go through each value in the vIn array (corresponding _<br>     with sheet S) and find the value in vChk (corresponding _<br>     with columns b,C of r)</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lC = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vIn, 2)<br>        <SPAN style="color:#00007F">For</SPAN> lR = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vIn, 1)<br>            <SPAN style="color:#007F00">'find the value</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> lChk = 1 <SPAN style="color:#00007F">To</SPAN> UBChk<br>                <SPAN style="color:#00007F">If</SPAN> vIn(lR, lC) = vChk(lChk, 1) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> lChk<br>            <SPAN style="color:#007F00">'check if the value was found</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> lChk <= UBChk <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">' Add the comment, which is the string in the second column of vChk</SPAN><br>                AddCmnt rIn.Offset(lR - 1, lC - 1), <SPAN style="color:#00007F">CStr</SPAN>(vChk(lChk, 2))<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> lR<br>    <SPAN style="color:#00007F">Next</SPAN> lC<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> AddCmnt(rCl <SPAN style="color:#00007F">As</SPAN> Range, sCmnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">With</SPAN> rCl<br>         .AddComment sCmnt<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[deltas247]

Sorry for my late reply, I did not get the email message until this morning.

Thank you sir for getting me on the correct path. I had to modify the AddCmnt Sub call, but only so that it included other relevant project information from sheet "S". It was mostly formatting and extra data, but one issue was the range not recognizing frozen cells /w data validation as values, I modified the layout to resolve. I also changed the comment dynamic so that future data would supplant the old. The use of the Triple loop was rather elegant, especially to simulate a matrix.

I am in the USA so you may not see this until later, but Cheers to you sir. -D

For Each ws In ActiveWorkbook.Worksheets
For Each cmt In ws.Comments
cmt.Delete
Next cmt
Next ws

Set rangeIn = Sheets("S").Range("A1")
Set rangeCheck = Sheets("R").Range("A1:A625")
varInput = rangeIn.CurrentRegion.Value
varCheck = rangeCheck.CurrentRegion.Value
UBCheck = UBound(varCheck, 1)

For longColumn = 1 To UBound(varInput, 2)
For longRow = 1 To UBound(varInput, 1)
For longCheck = 1 To UBCheck
If varInput(longRow, longColumn) = varCheck(longCheck, 1) Then Exit For
Next longCheck
If longCheck <= UBCheck Then
' Add the comment, which is the string in the second column of varCheck
AddCmnt rangeIn.Offset(longRow - 1, longColumn - 1), CStr(varCheck(longCheck, 2)) & Chr(10) & CStr(varCheck(longCheck, 3)) & Chr(10) & CStr(varCheck(longCheck, 5))
End If
Next longRow
Next longColumn

For Each ws In ActiveWorkbook.Worksheets
For Each cmt In ws.Comments
cmt.Shape.TextFrame.AutoSize = True
With cmt.Shape.TextFrame.Characters.Font
.Name = "Arial"
.Size = 20
.FontStyle = "Bold"
End With
Next cmt
Next ws