Sure...here it is:
Dim CommentValue As String, sPayer As String, cell As Range
Dim wks1 As Worksheet
Dim sReportName As String

Dim i As Integer, iRow As Long
Dim c As Object

'' WHENEVER THERE'S A COLUMN CHANGE TO THIS REPORT,
'' THESE CONSTANTS HAVE TO CHANGE - BEGIN
Const cStartRange = 19
Const cLastNameTab_Amrix_Targeting = 5
Const cLastNamePCS_inVentiv_Targets = 8
Const cLastNameTab_Amrix_12_Mth_TRx_Trend = 5
'' WHENEVER THERE'S A COLUMN CHANGE TO THIS REPORT,
'' THESE CONSTANTS HAVE TO CHANGE - END

Const cEndRange = cStartRange + 9

Set wks1 = ActiveSheet

sReportName = IIf(InStr(wks1.Name, "Amrix Targeting") < 0, "Amrix
Targeting", _
IIf(InStr(wks1.Name, "PCS-inVentiv Targets") < 0, "PCS-inVentiv
Targets", _
IIf(InStr(wks1.Name, "Amrix 12 Mth TRx Trend") < 0, "Amrix 12 Mth TRx
Trend", _
IIf(InStr(wks1.Name, "Payer") < 0, "Payer", "Null"))))

With wks1

For iRow = 2 To 60000

CommentValue = "No qualifying payer plans"
For i = cStartRange To cEndRange

If Range("C" & iRow).Value = "" Then
GoTo EndRows
Else

sPayer = CStr(Application.VLookup(Range("C" & iRow).Value,
Range("Payer!C:ZZ"), i - 2, False))
If sPayer < "" And Left(sPayer, 5) < "Error" Then
If i = cStartRange Then
CommentValue = sPayer
Else
CommentValue = CommentValue & Chr(10) & sPayer
End If
Else
i = cEndRange
End If
End If
Next i

If sReportName = "Amrix Targeting" Then
Range(Cells(iRow, cLastNameTab_Amrix_Targeting), _
Cells(iRow, cLastNameTab_Amrix_Targeting)).NoteText _
Text:=CommentValue
ElseIf sReportName = "PCS-inVentiv Targets" Then
Range(Cells(iRow, cLastNamePCS_inVentiv_Targets), _
Cells(iRow, cLastNamePCS_inVentiv_Targets)).NoteText _
Text:=CommentValue
ElseIf sReportName = "Amrix 12 Mth TRx Trend" Then
Range(Cells(iRow, cLastNameTab_Amrix_12_Mth_TRx_Trend), _
Cells(iRow, cLastNameTab_Amrix_12_Mth_TRx_Trend)).NoteText _
Text:=CommentValue
End If

Next iRow

EndRows:

For Each c In ActiveSheet.Comments
c.Shape.Width = 180
c.Shape.Height = 144
Next c

End With

End Sub

"Thomas [PBD]" wrote:

ambushsinger,

Could you post the entire macro here?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"ambushsinger" wrote:

I'm trying to programmatically insert comments into columns on multiple
worksheets in a workbook.
Problem is that it works on 2 of the 3 sheets and not the 3rd.
There's a VLookup that matches the data in one column on each sheet by
looking for a number. But on the 3rd sheet it craps out.
I'm desperate for an answer right now...can anyone help?

sPayer = CStr(Application.VLookup(Range("C" & iRow).Value,
Range("Payer!C:ZZ"), i - 2, False))