How can I use VBA Excel functions to make this easier?

[gijames]

Sub GetQMevalData()
'
' declare my variable types to be used in the Macro
Dim EvalFileName, currentMRTsheet, QMworksheetName, path, ci1, ci2, ci3, cC1, cC2, cC3, cA1, cA2, cA3, cR1, cR2, cR3, cE1, cE2, cE3 As String
' Define the current MRT worksheet
currentMRTsheet = ActiveSheet.Name
' Find the QM eval filename, workbook, from the MRT cell F2
EvalFileName = Range("F2")
' OPEN the QM eval workbook, and get the worksheet name
path = ThisWorkbook.path & "\"
Workbooks.Open (path & EvalFileName), ReadOnly
QMworksheetName = ActiveSheet.Name
' Switch back to the MRT workbook and populate data into the generated MRT worksheet
Workbooks(1).Activate
' Starting to pull values from QM Eval
' the CSR's name
Worksheets(currentMRTsheet).Range("B2").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,9,4)"
' the QM contact name
Worksheets(currentMRTsheet).Range("C2").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,12,4)"
' the Member's account number
Worksheets(currentMRTsheet).Range("D2").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,34,4)"
' the Viewer INUM
Worksheets(currentMRTsheet).Range("E2").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,33,4)"
' the CSR's overall score for the call
Worksheets(currentMRTsheet).Range("C4").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Evaluation"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),14)"
' Initiatives Scored and Comments
Worksheets(currentMRTsheet).Range("D10").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Educate caller on best use of client's plan design to maximize member and client cost saving."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D11").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Educate caller on best use of Medco's products and services."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("E10").Formula = "=IF(OR(D10=""Demonstrated"",D10=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing plan information to the caller. "")"
Worksheets(currentMRTsheet).Range("E11").Formula = "=IF(OR(D11=""Demonstrated"",D11=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate explaining autocharge or eCheck enrollment according to the August 2008 DYK "")"
' Commuications Scored and Comments
Worksheets(currentMRTsheet).Range("D13").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Addressed caller by title and last name throughout the call."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D14").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Demonstrated attentiveness and responded in a manner that indicates active listening."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D15").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Identified and acknowledged the caller's emotion when a heightened emotional state is expressed; appropriately acknowledged significant life events."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D16").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Accepts responsibility and apologizes when Medco processes, products or plan design have not met the caller's expectations, and responds positively to member criticism."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D17").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Spoke with voice inflection and used appropriate word choice to demonstrate interest, respect, patience, and willingness to assist."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D18").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Asked before placing the caller on hold; set a hold time expectation and revisited to inform them of progress; thanked the caller for holding."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("E13").Formula = "=IF(OR(D13=""Demonstrated"",D13=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate use of the caller’s last name either during the greeting and throughout the call or either of the two"")"
' Worksheets(currentMRTsheet).Range("E14").Formula = "=IF(OR(D14=""Demonstrated"",D14=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate attentiveness due to one or a combination of the following... asking again for information previously asked for, not acknowledging caller’s questions or business related statements"")"
Worksheets(currentMRTsheet).Range("E14").Formula = "=IF(OR(D14=""Demonstrated"",D14=""Not Expected""),"""",AB15)"
Worksheets(currentMRTsheet).Range("E15").Formula = "=IF(OR(D15=""Demonstrated"",D15=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate sincere tone and/or identify the specific emotion either explicitly communicated or implied by the caller"")"
Worksheets(currentMRTsheet).Range("E16").Formula = "=IF(OR(D16=""Demonstrated"",D16=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate accepting responsibility when caller’s expectation is not met."")"
Worksheets(currentMRTsheet).Range("E17").Formula = "=IF(OR(D17=""Demonstrated"",D17=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate gratitude, interest, respect, patience, and willingness to assist. "")"
Worksheets(currentMRTsheet).Range("E18").Formula = "=IF(OR(D18=""Demonstrated"",D18=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate the hold procedure."")"
' Accuracy Scored and Comments
Worksheets(currentMRTsheet).Range("D20").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Actions taken demonstrate adherence to SOPs and processes.*"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D21").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Provided accurate information to ensure first call resolution."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D22").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Provided complete information to ensure first call resolution."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("E20").Formula = "=IF(OR(D20=""Demonstrated"",D20=""Not Expected""),"""",""Opportunity Missed to demonstrate an SOP or Accredo specific process"")"
Worksheets(currentMRTsheet).Range("E21").Formula = "=IF(OR(D21=""Demonstrated"",D21=""Not Expected""),"""",""Opportunity Missed to provide accurate information"")"
Worksheets(currentMRTsheet).Range("E22").Formula = "=IF(OR(D22=""Demonstrated"",D22=""Not Expected""),"""",""Opportunity Missed to provide comprehensive information to resolve the call."")"
' Regulatory Scored and Comments
Worksheets(currentMRTsheet).Range("D24").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Protected patient IHI."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D25").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Followed procedures related to brand/generic linking."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D26").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Followed procedures related to counseling."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D27").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Followed procedures related to medication events (ENCs)."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("E24").Formula = "=IF(OR(D24=""Demonstrated"",D24=""Not Expected""),"""",""Disclosed Protected Health Information"")"
Worksheets(currentMRTsheet).Range("E25").Formula = "=IF(OR(D25=""Demonstrated"",D25=""Not Expected""),"""",""Opportunity Missed in following brand/generic procedures"")"
Worksheets(currentMRTsheet).Range("E26").Formula = "=IF(OR(D26=""Demonstrated"",D26=""Not Expected""),"""",""Opportunity Missed in following counseling procedures"")"
Worksheets(currentMRTsheet).Range("E27").Formula = "=IF(OR(D27=""Demonstrated"",D27=""Not Expected""),"""",""Opportunity Missed in preventing Medication Events"")"
' Effectiveness Scored and Comments
Worksheets(currentMRTsheet).Range("D29").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Answers call immediately."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D30").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Probed/Paraphrased to gather relevant information in order to clarify the caller's need/situation."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D31").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Interjected and redirected the caller; discussed pertinent information with the caller."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0)-1,4)"
Worksheets(currentMRTsheet).Range("D32").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Interjected and redirected the caller; discussed pertinent information with the caller."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D33").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Confidently provides information in an organized, understandable, and logical manner."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D34").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Navigated directly to understand and solve the problem."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D35").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Utilized system tools and people resources appropriately."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("D36").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Confirmed understanding of the actions taken and used a closing appropriate to the call."",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Worksheets(currentMRTsheet).Range("E29").Formula = "=IF(OR(D29=""Demonstrated"",D29=""Not Expected""),"""",""Opportunity Missed in answering call immediately"")"
Worksheets(currentMRTsheet).Range("E30").Formula = "=IF(OR(D30=""Demonstrated"",D30=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate probing and/or paraphrasing to gain clarification of caller’s need"")"
Worksheets(currentMRTsheet).Range("E31").Formula = "=IF(OR(D31=""Demonstrated"",D31=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing options to caller’s need"")"
Worksheets(currentMRTsheet).Range("E32").Formula = "=IF(OR(D32=""Demonstrated"",D33=""Not Expected""),"""",""Opportunity Missed to consistently discuss pertinent information with the caller"")"
Worksheets(currentMRTsheet).Range("E33").Formula = "=IF(OR(D33=""Demonstrated"",D34=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing information with confidence"")"
Worksheets(currentMRTsheet).Range("E34").Formula = "=IF(OR(D34=""Demonstrated"",D35=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate application navigation as it relates to the context of the call"")"
Worksheets(currentMRTsheet).Range("E35").Formula = "=IF(OR(D35=""Demonstrated"",D36=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate use of tools or people resources to resolve caller’s issue"")"
Worksheets(currentMRTsheet).Range("E36").Formula = "=IF(OR(D36=""Demonstrated"",D37=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate extending further assistance or branding the call."")"
'cE1 = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
'cE1 = Application.WorksheetFunction.Index('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)
'cE2 = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0)+1,4)"
'cE3 = cE1 + cE2
'cE3 = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0)+2,4)"
'Worksheets(currentMRTsheet).Range("F29").Value = cE1
'Worksheets(currentMRTsheet).Range("F29").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
'Worksheets(currentMRTsheet).Range("F29").Formula = "=INDEX('[" & EvalFileName & "]" & QMworksheetName & "'!A1:S200,MATCH(""Effectiveness Comments:"",'[" & EvalFileName & "]" & QMworksheetName & "'!C1:C200,0),4)"
Workbooks(EvalFileName).Close
End Sub

**** Also, I need for certain cells in currentMRTsheet to be able to pull (concatenate) several text comments from the other sheet so that... like cell near the end of this script, F29 should pull the data from the first indexed column 4 and the next 2 rows down..

 

[gijames]

Hey All,
I know with Thanksgiving here it may be slow for some of the experts to see my thread question and have time to reply. I do thank you all for the time in looking at it!

Also, if any of you experts could recommend a VBA Excel book(s) for novices in programming (I need lots of good syntax!) I would very much appreciate it

 

[gijames]

Any body have any useful insight?

Please don't be shy!

 

[gijames]

Hey All,
please let me know if I can help clarify any of the formula code I entered for the topic of this thread. I would like to be able to take the data from the exported HTML file (opening the file with Excel 2003), that is renamed to *.xls, and put that data into a sheet that is much easier for the customer service reps to understand.

I have everything figured out with what I do have above in the formula codes, EXCEPT for the part on trying to assign three variables. 1var and 2var would be separate values referenced from the HTML sheet (text comments) and 3var would be the two text strings (concatenated?) and made as a cell value on the sheet that will be given to the customer service reps. I am stuck! Your help will help me keep my sanity (j/k)

 

[gijames]

...
I have everything figured out with what I do have above in the formula codes, EXCEPT for the part on trying to assign three variables. 1var and 2var would be separate values referenced from the HTML sheet (text comments) and 3var would be the two text strings (concatenated?) and made as a cell value on the sheet that will be given to the customer service reps. I am stuck!

The two worksheets are also in separate workbooks. The nature of the HTML sheet is unique to every call, that is why I need a way to evaluate the cells in the HTML worksheet to pull the comments and scores for the workbook that will be a collection of all the customer service reps.

 

[Norie]

It might be difficult to see the question because there doesn't actually appear to be one.

All I can see in your original post is a whole lot of unformatted code.

If your thread title is the question then that doesn't make things clearer.

What is it you want to do faster and what do you mean by'VBA Excel functions'?

 

[gijames]

Thank you to the one, Smitty?, who helped format the code in the starting topic for this thread :D

Basically, I would like to know if...

the simplicity of the formulas I have in the code are the easiest way to have the code entered. Meaning, I have seen examples of something like Range(reference).value = "possible Excel worksheet function with proper syntax when pulling the data from a worksheet in a separate workbook"
... because I am a novice at this, I would like to know if the Range(reference).Formula = "whatever formula"

and if 'value' is a good way to pull data, how to use the someStrVar = Range(reference).value = someExcelwsfunction, in order to 'combine' several text strings from the HTML worksheet (this would be the bottom part of my thread question).

and... what books are good for the novice programmer for VBA, so that learning the syntax of setting up the statements will flow smoothly. I can hack something with good examples. But I would like to also know *why* it is that I have a code statement setup a particular way. (it took me forever to use the """" syntax for parts of the formulas in my above code).

The rest of my comments to this thread topic were to try to help with the *reason* as to what I am trying to accomplish... essentially I need to make a clearer worksheet for our customer service reps to follow when being scored for their calls. The system that generates their html sheets puts much too much stuff that is not necessary for them to have to follow. They only need the scorecard layout, and comments/coaching opportunities in the section of the score card , which is the final product I am putting together for our quality team. whew... did that help any more :D

 

[gijames]

I Think ... this is close to what will make this much *cleaner* to follow and is pretty close to meeting the points in my last post in this thread... thoughts?

Sub GetQMevalData()
'
' QM Eval filename and data to be used in formulas for populating MRT for CSO
' VBA testing 2009-11-19 by JSAL
'
' declare my variable types to be used in the Macro
Dim EvalFileName, currentMRTsheet, QMworksheetName, path, ci1, ci2, ci3, cC1, cC2, cC3, cA1, cA2, cA3, cR1, cR2, cR3, cE1, cE2, cE3 As String

' Define the current MRT worksheet
currentMRTsheet = ActiveSheet.Name

' Find the QM eval filename, workbook, from the MRT cell F2
EvalFileName = Range("F2")

' OPEN the QM eval workbook, and get the worksheet name
path = ThisWorkbook.path & "\"
Workbooks.Open (path & EvalFileName), ReadOnly
QMworksheetName = ActiveSheet.Name

' Switch back to the MRT workbook and populate data into the generated MRT worksheet
Workbooks(1).Activate

' Starting to pull values from QM Eval

' the CSR's name
Worksheets(currentMRTsheet).Range("B2").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),9,4)

' the QM contact name
Worksheets(currentMRTsheet).Range("C2").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),12,4)

' the Member's account number
Worksheets(currentMRTsheet).Range("D2").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),34,4)

' the Viewer INUM
Worksheets(currentMRTsheet).Range("E2").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),33,4)

' the CSR's overall score for the call
Worksheets(currentMRTsheet).Range("C4").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Evaluation", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),14)

' Initiatives Scored and Comments
Worksheets(currentMRTsheet).Range("D10").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Educate caller on best use of client's plan design to maximize member and client cost saving.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D11").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Educate caller on best use of Medco's products and services.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)

Worksheets(currentMRTsheet).Range("E10").Formula = "=IF(OR(D10=""Demonstrated"",D10=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing plan information to the caller.  "")"
Worksheets(currentMRTsheet).Range("E11").Formula = "=IF(OR(D11=""Demonstrated"",D11=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate explaining autocharge or eCheck enrollment according to the August 2008 DYK  "")"

' Commuications Scored and Comments
Worksheets(currentMRTsheet).Range("D13").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Addressed caller by title and last name throughout the call.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D14").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Demonstrated attentiveness and responded in a manner that indicates active listening.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D15").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Identified and acknowledged the caller's emotion when a heightened emotional state is expressed; appropriately acknowledged significant life events.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D16").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Accepts responsibility and apologizes when Medco processes, products or plan design have not met the caller's expectations, and responds positively to member criticism.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D17").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Spoke with voice inflection and used appropriate word choice to demonstrate interest, respect, patience, and willingness to assist.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D18").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Asked before placing the caller on hold; set a hold time expectation and revisited to inform them of progress; thanked the caller for holding.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)

Worksheets(currentMRTsheet).Range("E13").Formula = "=IF(OR(D13=""Demonstrated"",D13=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate use of the caller’s last name either during the greeting and throughout the call or either of the two"")"
' Worksheets(currentMRTsheet).Range("E14").Formula = "=IF(OR(D14=""Demonstrated"",D14=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate attentiveness due to one or a combination of the following... asking again for information previously asked for, not acknowledging caller’s questions or business related statements"")"
Worksheets(currentMRTsheet).Range("E14").Formula = "=IF(OR(D14=""Demonstrated"",D14=""Not Expected""),"""",AB15)"
Worksheets(currentMRTsheet).Range("E15").Formula = "=IF(OR(D15=""Demonstrated"",D15=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate sincere tone and/or identify the specific emotion either explicitly communicated or implied by the caller"")"
Worksheets(currentMRTsheet).Range("E16").Formula = "=IF(OR(D16=""Demonstrated"",D16=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate accepting responsibility when caller’s expectation is not met."")"
Worksheets(currentMRTsheet).Range("E17").Formula = "=IF(OR(D17=""Demonstrated"",D17=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate gratitude, interest, respect, patience, and willingness to assist.  "")"
Worksheets(currentMRTsheet).Range("E18").Formula = "=IF(OR(D18=""Demonstrated"",D18=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate the hold procedure."")"

' Accuracy Scored and Comments
Worksheets(currentMRTsheet).Range("D20").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Actions taken demonstrate adherence to SOPs and processes.*", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D21").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Provided accurate information to ensure first call resolution.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D22").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Provided complete information to ensure first call resolution.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)

Worksheets(currentMRTsheet).Range("E20").Formula = "=IF(OR(D20=""Demonstrated"",D20=""Not Expected""),"""",""Opportunity Missed to demonstrate an SOP or Accredo specific process"")"
Worksheets(currentMRTsheet).Range("E21").Formula = "=IF(OR(D21=""Demonstrated"",D21=""Not Expected""),"""",""Opportunity Missed to provide accurate information"")"
Worksheets(currentMRTsheet).Range("E22").Formula = "=IF(OR(D22=""Demonstrated"",D22=""Not Expected""),"""",""Opportunity Missed to provide comprehensive information to resolve the call."")"

' Regulatory Scored and Comments
Worksheets(currentMRTsheet).Range("D24").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Protected patient IHI.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D25").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Followed procedures related to brand/generic linking.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D26").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Followed procedures related to counseling.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D27").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Followed procedures related to medication events (ENCs).", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)

Worksheets(currentMRTsheet).Range("E24").Formula = "=IF(OR(D24=""Demonstrated"",D24=""Not Expected""),"""",""Disclosed Protected Health Information"")"
Worksheets(currentMRTsheet).Range("E25").Formula = "=IF(OR(D25=""Demonstrated"",D25=""Not Expected""),"""",""Opportunity Missed in following brand/generic procedures"")"
Worksheets(currentMRTsheet).Range("E26").Formula = "=IF(OR(D26=""Demonstrated"",D26=""Not Expected""),"""",""Opportunity Missed in following counseling procedures"")"
Worksheets(currentMRTsheet).Range("E27").Formula = "=IF(OR(D27=""Demonstrated"",D27=""Not Expected""),"""",""Opportunity Missed in preventing Medication Events"")"

' Effectiveness Scored and Comments
Worksheets(currentMRTsheet).Range("D29").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Answers call immediately.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D30").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Probed/Paraphrased to gather relevant information in order to clarify the caller's need/situation.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D31").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Interjected and redirected the caller; discussed pertinent information with the caller.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0)-1,4)
Worksheets(currentMRTsheet).Range("D32").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Interjected and redirected the caller; discussed pertinent information with the caller.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D33").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Confidently provides information in an organized, understandable, and logical manner.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D34").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Navigated directly to understand and solve the problem.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D35").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Utilized system tools and people resources appropriately.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)
Worksheets(currentMRTsheet).Range("D36").Value = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"),Application.WorksheetFunction.Match("Confirmed understanding of the actions taken and used a closing appropriate to the call.", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"),0),4)

Worksheets(currentMRTsheet).Range("E29").Formula = "=IF(OR(D29=""Demonstrated"",D29=""Not Expected""),"""",""Opportunity Missed in answering call immediately"")"
Worksheets(currentMRTsheet).Range("E30").Formula = "=IF(OR(D30=""Demonstrated"",D30=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate probing and/or paraphrasing to gain clarification of caller’s need"")"
Worksheets(currentMRTsheet).Range("E31").Formula = "=IF(OR(D31=""Demonstrated"",D31=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing options to caller’s need"")"
Worksheets(currentMRTsheet).Range("E32").Formula = "=IF(OR(D32=""Demonstrated"",D32=""Not Expected""),"""",""Opportunity Missed to consistently discuss pertinent information with the caller"")"
Worksheets(currentMRTsheet).Range("E33").Formula = "=IF(OR(D33=""Demonstrated"",D33=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate providing information with confidence"")"
Worksheets(currentMRTsheet).Range("E34").Formula = "=IF(OR(D34=""Demonstrated"",D34=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate application navigation as it relates to the context of the call"")"
Worksheets(currentMRTsheet).Range("E35").Formula = "=IF(OR(D35=""Demonstrated"",D35=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate use of tools or people resources to resolve caller’s issue"")"
Worksheets(currentMRTsheet).Range("E36").Formula = "=IF(OR(D36=""Demonstrated"",D36=""Not Expected""),"""",""Opportunity Missed to either completely or partially demonstrate extending further assistance or branding the call."")"


cE1 = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"), Application.WorksheetFunction.Match("Effectiveness Comments:", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"), 0), 4)
cE2 = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"), Application.WorksheetFunction.Match("Effectiveness Comments:", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"), 0) + 1, 4)
cE3 = Application.WorksheetFunction.Index(Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("A1:S200"), Application.WorksheetFunction.Match("Effectiveness Comments:", Workbooks(EvalFileName).Worksheets(QMworksheetName).Range("C1:C200"), 0) + 2, 4)

Worksheets(currentMRTsheet).Range("F29").Value = cE1 + cE2 + cE3

Workbooks(EvalFileName).Close

End Sub

 

[Norie]

I'm sorry but that doesn't help any further - what in words is this code meant to do?