VBA to Countif text results across a row of data

[alanma]

Hi guys,

New-ish here, but already had a lot of help - even if most of you didn't know it!
But now in a bit of a bind, so time to ask, rather than use what was already available!

I'm in the process of creating a spreadsheet for my employer that will enable us to accurately measure employee performance against a range of KPI's.

Being a VBA newbie, I've so far managed to get each new step working, thanks to the likes of Mr Excel! - Google was very helpful!

To get to the point:
I now need to calculate the results on the last cell of each row. There are 67 Columns of data per record, and the sheet is designed to automatically create a new row as each new record is entered via a user-form, using 'Dim Rowcount As Long and Offset Rowcount to populate individual cells.

The KPI's that the employee is being measured against, are split into three sections, with each section given a percentage score (100%) which is then averaged across the three to arrive at a final percentage score. Section one consists of 23 "Yes" or "No" questions, section two consists of 5 "Correct", "Partial", or "Incorrect" questions, and section three deals in the the time frame the employee to took to process each job, again, in a text not numerical, format.

Now I've struck a bit of a brick wall. I've been able to workout the calculations needed in individual cells on the end of each row, to give me my answer, but when it comes to replicating the calculation down across every single row in advance, it's causing more hassle than it's worth, so I'm looking for some assistance with a VBA solution that will fit in with the rest of my VBA solutions. Google wasn't quite enough help this time!

The end result is that the last cell in each row needs to describe in text whether the employee was "Highly-Compliant", "Compliant", "Partially-Compliant", "Low-Compliant", or "Non-Compliant" with the KPI's

I should add, that I'm using Excel 2007, on Windows 7.

Any assistance would be greatly appreciated.

Alan

 

[svendiamond]

The end result is that the last cell in each row needs to describe in text whether the employee was "Highly-Compliant", "Compliant", "Partially-Compliant", "Low-Compliant", or "Non-Compliant" with the KPI's

What are the qualifications for each of these? A certain percentage? What cells are your data in?

 

[alanma]

Cells A thru F contain the individual record details, such as the job no., employee, and who has audited it. After that, there are three sections, each with its own score of 100%, which is then averaged to arrive at the final score:

Section 1: 23 Yes or No questions from G thru to AY totalling 100% for that section = approx 4.347% per "Yes" and 0% for "No"
Section 2: 5 "Correct", "Partial", or "Incorrect" on BA thru BI also totalling 100% = 20% for each "Correct", 10% for each "Partial" and 0% for "Incorrect"
(note both of these sections have their answers in every other column to allow for comments from the auditors against each mark)
The final section is answered in BK and has one question that is worth 100% if the condition is met, 50% if partially met, and 0% if not met. This is in relation to the time it takes the employee to complete the task at hand, in a text format.

All three columns are then averaged across the row to arrive at a final percentage score, which is then translated into to text for easier understanding.
Thus: if the averaged score of all three sections is greater than 95% then the employee has a "Highly Compliant" Score. If the averaged score is 90-95%, then the employee has a "Compliant" Score, and so on.

The cell that the final results need to go in for each record is BN

Hope that clarifies things?

 

[svendiamond]

Thus: if the averaged score of all three sections is greater than 95% then the employee has a "Highly Compliant" Score. If the averaged score is 90-95%, then the employee has a "Compliant" Score, and so on.

The cell that the final results need to go in for each record is BN

Let's say the average scores go into column BN... then can't you just have, in column BO, a simple thing like:

=IF(BN2>=0.95,"Highly Compliant",IF(BN2>0.90,"Compliant".... and so on and so forth...?

 

[alanma]

Thanks for your thoughts Sven; That's pretty much what I've already had .
The issue at hand is the fact that my number of records will be constantly expanding as more employees, and more records per employee are added over time.
Eventually this could end up with several thousand records. I don't particularly want to enter those calculations manually into each cell in columns BN, and BO for that many records in advance, or individually for that matter!
My current setup uses a UserForm to enter the details, which when saved populates all the necessary fields. I've managed this using an Offset(RowCount, xx) function.
I was wanting to do the same for the above calculations.
I tried using macro-recorder, and came up with this:

Sub ScoringAllRecords()
'
' ScoringAllRecords Macro
' keeps score of all records
'
Range("BN3").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(COUNTIF(RC[-62]:RC[-18],""=Yes"")*0.043478+COUNTIF(RC[-16]:RC[-8],""=Correct"")*0.2+COUNTIF(RC[-16]:RC[-8],""=Partial"")*0.1+COUNTIF(RC[-6],""=* Seconds"")+COUNTIF(RC[-6],""=* Urban"")*0.5+COUNTIF(RC[-6],""=* Rural"")*0.5+COUNTIF(RC[-6],""=As Available""))/3"
Range("BN3").Select
Selection.Style = "Percent"
Range("BO3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>=95%,""High Compliance"",IF(RC[-1]>90%,""Compliant"",IF(RC[-1]>85%,""Partial Compliance"",IF(RC[-1]>80%,""Low Compliance"",IF(RC[-1]<80%,""Non Compliant"",)))))"
Range("A1").Select
End Sub

Needless to say, this was done only in the first row of records, hence the "BN3" and "BO3" in the Range data! I figure it should be something like just "BN" and "BO". Is that correct?
The problem is to build this into my ".Offset(RowCount, xx)" details in such a way that makes sense to VBA.
Based on my current RowCount these two items would be in RowCount, 66 and RowCount, 67 respectively, but I can't quite work out the VBA code to do it correctly.

Would it help if I posted the code that I'm using so far to get the data from the userform into the appropriate fields?

Thoughts?

 

[svendiamond]

I don't understand why you're using VBA to enter your formulas. Why not just enter the formulas into BN3 and BO3 and fill down?