Application.VLookup

A

ambushsinger

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

ambushsinger

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 said:
ambushsinger,

Could you post the entire macro here?

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


ambushsinger said:
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))
Click to expand...
Click to expand...
 
D

Dave Peterson

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


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 said:
ambushsinger,

Could you post the entire macro here?

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


ambushsinger said:
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))
Click to expand...
Click to expand...
Click to expand...
 
A

ambushsinger

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 said:
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).


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 said:
ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
T

Thomas [PBD]

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 said:
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 said:
ambushsinger,

Could you post the entire macro here?

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


ambushsinger said:
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))
Click to expand...
Click to expand...
Click to expand...
 
A

ambushsinger

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 said:
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 said:
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 said:
ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
T

Thomas [PBD]

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 said:
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 said:
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 said:
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

:

ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
T

Thomas [PBD]

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 said:
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 said:
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 said:
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.


:

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

:

ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
A

ambushsinger

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

Thanks again
Tom

Thomas said:
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 said:
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 said:
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

:

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.


:

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

:

ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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))
Click to expand...

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

If that's not it, check the spelling of the worksheet name.
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 said:
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).


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

:

ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
T

Thomas [PBD]

ambushsinger,

You're very welcome, thanks for the feedback.

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


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

Thanks again
Tom

Thomas said:
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 said:
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.


:

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

:

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.


:

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

:

ambushsinger,

Could you post the entire macro here?

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


:

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))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Best way to run Find or Vlookup down specific column in range 2
Use number in text box to look up value in a workbook sheet 1
Add value to the content of a cell with Vlookup 2
Part of VB not working 1
Lookup Data in external workbook 3
Problem with worksheet functions and errors 2
424 error on a Worksheet Change Macro 2
Add loop to Vlookup 1

Top