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

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

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

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

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

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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

You got it to work on the 12 month trend tab?
That's what I want it to do.
It doesn't need to work on the payer tab.
I'm trying to get it to insert comments on the Last Name column of the 12
Mth trend tab.
If you got it to work please let me know what you did.

Tom

"Thomas [PBD]" wrote:

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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

ambushsinger,

Heh. I didnt do anything to the code. In my tabs, here is the process I
used:
Payer Tab:
C2 = "2"
C3 = "3"
S2 = "Help2"
T2 = "Help2.1"

Amrix 12 Mth TRx Trend (I copied this name from the VBA script) tab:
C2 = "2"
C3 = "3"

Returns:
E2 Comment: "Help2 <br Help2.1"
E3 Comment: "No Qualifying Payer Plans"

If you would like, I will repost the VBA script, just in case I changed
something. The way I saw it work was to lookup, 2 from 12 Mth(C2) and find
it in Payer, return Comment Help2 and Help2.1 into E2. Lookup 3 from 12
Mth(C3) and look for it, but not find it in Payer (returning no qualifying).

I guess you could ensure the Tab names and records matching from one tab to
the next. As well, the Vlookup starts in Column S on the Payer tab, ensure
that it is not blank.

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


"ambushsinger" wrote:

You got it to work on the 12 month trend tab?
That's what I want it to do.
It doesn't need to work on the payer tab.
I'm trying to get it to insert comments on the Last Name column of the 12
Mth trend tab.
If you got it to work please let me know what you did.

Tom

"Thomas [PBD]" wrote:

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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

Correction:

Payer Tab C3 is blank and 3 does not exist on the tab. Therefore it would
return nothing and force "No Qualifying". Sorry, I mis-spoke.

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


"Thomas [PBD]" wrote:

ambushsinger,

Heh. I didnt do anything to the code. In my tabs, here is the process I
used:
Payer Tab:
C2 = "2"
C3 = "3"
S2 = "Help2"
T2 = "Help2.1"

Amrix 12 Mth TRx Trend (I copied this name from the VBA script) tab:
C2 = "2"
C3 = "3"

Returns:
E2 Comment: "Help2 <br Help2.1"
E3 Comment: "No Qualifying Payer Plans"

If you would like, I will repost the VBA script, just in case I changed
something. The way I saw it work was to lookup, 2 from 12 Mth(C2) and find
it in Payer, return Comment Help2 and Help2.1 into E2. Lookup 3 from 12
Mth(C3) and look for it, but not find it in Payer (returning no qualifying).

I guess you could ensure the Tab names and records matching from one tab to
the next. As well, the Vlookup starts in Column S on the Payer tab, ensure
that it is not blank.

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


"ambushsinger" wrote:

You got it to work on the 12 month trend tab?
That's what I want it to do.
It doesn't need to work on the payer tab.
I'm trying to get it to insert comments on the Last Name column of the 12
Mth trend tab.
If you got it to work please let me know what you did.

Tom

"Thomas [PBD]" wrote:

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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

Got it....yeah!!!!
Thank you Tom, Dave.
Everything is working now. I owe you guys lunch...lol

Thanks again
Tom

"Thomas [PBD]" wrote:

Correction:

Payer Tab C3 is blank and 3 does not exist on the tab. Therefore it would
return nothing and force "No Qualifying". Sorry, I mis-spoke.

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


"Thomas [PBD]" wrote:

ambushsinger,

Heh. I didnt do anything to the code. In my tabs, here is the process I
used:
Payer Tab:
C2 = "2"
C3 = "3"
S2 = "Help2"
T2 = "Help2.1"

Amrix 12 Mth TRx Trend (I copied this name from the VBA script) tab:
C2 = "2"
C3 = "3"

Returns:
E2 Comment: "Help2 <br Help2.1"
E3 Comment: "No Qualifying Payer Plans"

If you would like, I will repost the VBA script, just in case I changed
something. The way I saw it work was to lookup, 2 from 12 Mth(C2) and find
it in Payer, return Comment Help2 and Help2.1 into E2. Lookup 3 from 12
Mth(C3) and look for it, but not find it in Payer (returning no qualifying).

I guess you could ensure the Tab names and records matching from one tab to
the next. As well, the Vlookup starts in Column S on the Payer tab, ensure
that it is not blank.

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


"ambushsinger" wrote:

You got it to work on the 12 month trend tab?
That's what I want it to do.
It doesn't need to work on the payer tab.
I'm trying to get it to insert comments on the Last Name column of the 12
Mth trend tab.
If you got it to work please let me know what you did.

Tom

"Thomas [PBD]" wrote:

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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

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

ambushsinger,

You're very welcome, thanks for the feedback.

--
--Thomas [PBD]
Working hard to make working easy.


"ambushsinger" wrote:

Got it....yeah!!!!
Thank you Tom, Dave.
Everything is working now. I owe you guys lunch...lol

Thanks again
Tom

"Thomas [PBD]" wrote:

Correction:

Payer Tab C3 is blank and 3 does not exist on the tab. Therefore it would
return nothing and force "No Qualifying". Sorry, I mis-spoke.

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


"Thomas [PBD]" wrote:

ambushsinger,

Heh. I didnt do anything to the code. In my tabs, here is the process I
used:
Payer Tab:
C2 = "2"
C3 = "3"
S2 = "Help2"
T2 = "Help2.1"

Amrix 12 Mth TRx Trend (I copied this name from the VBA script) tab:
C2 = "2"
C3 = "3"

Returns:
E2 Comment: "Help2 <br Help2.1"
E3 Comment: "No Qualifying Payer Plans"

If you would like, I will repost the VBA script, just in case I changed
something. The way I saw it work was to lookup, 2 from 12 Mth(C2) and find
it in Payer, return Comment Help2 and Help2.1 into E2. Lookup 3 from 12
Mth(C3) and look for it, but not find it in Payer (returning no qualifying).

I guess you could ensure the Tab names and records matching from one tab to
the next. As well, the Vlookup starts in Column S on the Payer tab, ensure
that it is not blank.

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


"ambushsinger" wrote:

You got it to work on the 12 month trend tab?
That's what I want it to do.
It doesn't need to work on the payer tab.
I'm trying to get it to insert comments on the Last Name column of the 12
Mth trend tab.
If you got it to work please let me know what you did.

Tom

"Thomas [PBD]" wrote:

Ambushsinger,

So, from what I am seeing, you are getting Error Code 2042. That basically
means that the Vlookup is pulling an #N/A match not found error. I tested
this code really not knowing what I was looking for, but started to get an
idea. I got the comment to return on my Tabs titled "Amrix Targeting"
"PCS-inVentiv Targets" and "Amrix 12 Mth TRx Trend", but could not get it to
work on "Payer" (intentional).
Looks like what it comes down to is ensuring that the tab you are working on
needs to have cell values in C2:C6000 (if one of those cells between that
range have a blank it will end), and in the tab "Payer" row C must have a
corresponding record to one of those cells. It will pull the values from
Columns S:AB and place it into the comment field considering that the value
pulled is not blank, adding each column to the comment.

I see what it does. What isn't it doing?

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


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