That means that there is no worksheet named Payer in the activeworkbook.

You can specify the workbook that holds that worksheet:

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

If that payer worksheet is in the same workbook as wks1.

If that's not it, check the spelling of the worksheet name.

ambushsinger wrote:

DEFINITELY in the right direction...but I'm getting the message "Subscript
out of range" at the following line:
sPayer = CStr(Application.VLookup(.Range("C" & iRow).Value, _
worksheets("Payer").range(C:ZZ"), i - 2, False))

"Dave Peterson" wrote:

You're using:

With wks1

but you're not qualifying any of the ranges in your code:

Dim CommentValue As String, sPayer As String, cell As Range
Dim wks1 As Worksheet
Dim sReportName As String

Dim i As Long
Dim iRow As Long
Dim c As Comment ' why use 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
'or to avoid looking at 60000 rows
for irow = 2 to .cells(.rows.count,"C").end(xlup).row

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, _
worksheets("Payer").range(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

Notice the additional dots in front of .range() and .cells(). This means that
they belong to the object in the previous with statement (wks1 in this case).



ambushsinger wrote:

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))

--

Dave Peterson


--

Dave Peterson