Hi Howard,

Am Fri, 14 Feb 2014 08:55:13 -0800 (PST) schrieb L. Howard:

This is two of seven macros I am writing to do the exact same thing to seven different columns, G to M.

The greatest difference between them is the column B source for the comments text values, which are in blocks of seven rows on sheet "Comments". (A number in column B is the key to grab the Offset(,1) for the comment text back to sheet "Application" and the proper column & cell.

I have intended to run all seven from a single button using an Input box and a Select Case scenario. Input the column letter and call the proper macro. (the OP sent me a workbook with seven buttons, one at the top of each column thinking that is how it has to be)

I suppose a pro could write a single macro that would take the column letter input and do what I am doing with seven. I am not opposed to that but I feel I need to be able to at least read and understand enough of the code to make adjustments and explain it to a moderate degree.

try:

Sub Test()
Dim rngC As Range, c As Range
Dim AppRng As Range, ComRng As Range
Dim Start As Long
Dim MyCol As String

MyCol = Application.InputBox("Please enter a column character", _
"Column check", Type:=2)

If MyCol = "" Or MyCol = "False" Then Exit Sub

Select Case MyCol
Case "G"
Start = 2
Case "H"
Start = 9
Case "I"
Start = 16
Case "J"
Start = 23
Case "K"
Start = 30
Case "L"
Start = 37
Case "M"
Start = 44
End Select

With Sheets("Comments")
Set ComRng = .Range(.Cells(Start, 2), .Cells(Start + 6, 2))
End With

With Sheets("Application")
Set AppRng = .Range(.Cells(6, MyCol), _
.Cells(.Rows.Count, MyCol).End(xlUp))
End With

For Each rngC In AppRng
For Each c In ComRng
If c = rngC Then
rngC.ClearComments
rngC.AddComment c.Offset(, 1).Text
End If
Next
Next

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2