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