Convert-Calculate and Convert Again

[elsone31]

Here goes, I am new to learning VBA and what I need is way above my current capabilities, I have no idea how to even get started .

Provide for the coders:


Version - 2013
What you want it to do


A. I need the +A's, +B's, +C's and the +D's converted to 1's and all other responses converted to 0. The dashes need to be deleted.
B. I need the Like TEKS which are located in R4 and R5 to be counted cumulatively and a header row created for each with the total number included in row beneath the TEK (Format of the output file attached as well).
C. Next I will need to count the number 1's for each TEK as well
D. Calculate the percent correct - based only on the data the student has available
Example:

Student 3 has no data for TEST 3, so I would not want the denominator for that student to include any TEK data counts from that test.
E. Finally convert the percentages to numbers based on the rubric below
1 - > 50%
2 - 50% to 69%
3 - 70% to 84%
4 - < 85


3. Cell references, bookmark names, column letters, row NUMBERS, worksheets, styles, whatever pertains to the information at hand


My current worksheet has 177 rows and 222 columns but I would need to adjust based on future use.



I really appreciate any help I get on this and I hope I posted this correctly.

[Paul_Hossler]

1. What's a TEKS and how are they determined? There is no 'TEKS' on the input sheet

2. What's the difference between rows 5 and 6?

3. Some row 4 entries just have ()


4. Guessing that "I need the Like TEKS which are located in R4 and R5 to" means rows 4 and 5 are the 'TEKS' but each +A +B +C etc will / might /could have 2 different 'values' (M2 and 3.4(A) [R]), the same Mx in row 4 has different values in row 5. So what are the summarizing rules and the difference between rows 4 and 5?

5. Is row 6 important?

6. If you get the results, is there any reason to reformat the input sheet (e.g. convert to 1's and 0's)?

7. Some have text data (e.g. HF16 = +600). Should those be treated as a numerical 600 and converted to 0 IAW you A. above?


Guessing from Google for TEKS and your state flag

http://tea.texas.gov/index2.aspx?id=6148

but still need the rules to allocate rows 4 and row 5 values to TEKs.

[elsone31]

Yes, the TEKS are just to categorize the data cumulatively. Want to know student performance based on each TEK. Row five the TEKS are Content on Row six are Process TEKS, the questions are what we call dual coded with these TEKS.


It SHOULD be row six and not row four, thanks for catching that. Row 4 is not needed.

The way I am going to use the data I would prefer it to be 1 - 4 as that is what will drive my data set. The numeric data needs to be converted to 1's and 0's as well, because 1 means it is correct the 0 means it is incorrect, so any numeric data that is preceded by a + is correct the others are incorrect.

Thank you so much for helping me with this.

[Paul_Hossler]

Q1 - what do you mean 1- 4?

I understand the transform for the numerical data (I think)

By your rules, Student 1 C --> 0, +A -->1, 11 --> 0, 6 --> 0, dash cleared, etc.

13943




Q2 - are there 60 unique TEKS in your sample that you want as column headers?





3.1(A) [P]
3.1(B) [P]
3.1(C) [P]
3.1(D) [P]
3.1(E) [P]
3.1(F) [P]
3.1(G) [P]
3.2(A) [R]
3.2(B) [S]
3.2(C) [S]
3.2(D) [R]
3.3(A) [S]
3.3(E) [S]
3.3(F) [R]
3.3(H) [R]
3.4(A) [R]
3.4(B) [S]
3.4(C) [S]
3.4(D) [S]
3.4(E) [S]
3.4(F) [S]
3.4(G) [S]
3.4(H) [S]
3.4(I) [S]
3.4(J) [S]
3.4(K) [R]
3.5(A) [R]
3.5(B) [R]
3.5(C) [S]
3.5(D) [S]
3.5(E) [R]
3.6(A) [R]
3.6(B) [S]
3.6(C) [R]
3.6(D) [S]
3.7(A) [S]
3.7(B) [R]
3.7(C) [S]
3.7(D) [S]
3.7(E) [S]
3.8(A) [R]
3.8(B) [S]
3.9(A) [S]
3.9(B) [S]
3.9(D) [S]
3.9(E) [S]
4.1(A) [P]
4.1(B) [P]
4.1(D) [P]
4.1(E) [P]
4.1(F) [P]
4.2(A) [S]
4.2(D) [S]
4.2(E) [S]
4.2(F) [S]
4.2(H) [S]
4.4(A) [R]
4.4(B) [S]
4.4(G) [S]
4.5(B) [R]

[elsone31]

Yes your understanding is correct based on the example you gave. Also, the TEKS that start with the number 4 can be deleted.

The 1 - 4 will be used after the percentages are calculated.

E. Finally convert the percentages to numbers based on the rubric below

1 - > 50%
2 - 50% to 69%
3 - 70% to 84%
4 - < 85

Again thanks for helping with this.

[Paul_Hossler]

Been playing with this

WS Input is copied to WS Working and the 1's and 0's are figured out

WS Output has unique list of the Row 5 and Row 6 values as column headers

Right now, each student's 1 or 0 sums into one or two of the columns.

Some students have large values in the WS Output matrix. Is that expected, since you said 1 - 4

[elsone31]

Your current data is correct, the 1 - 4 will be applied as a rubric score after the percentages are calculated. This is awesome!!!!

Erica

[Paul_Hossler]

the 1 - 4 will be applied as a rubric score after the percentages are calculated


what are your rules again for calculating percents (not the rubric)

[elsone31]

Paul,

Divide the number correct (the 1's) by the total number of responses (1's + 0').

Erica

[Paul_Hossler]

1. I think you had your < and > reversed. I think it should have been

1 - < 50%
2 - 50% to 69%
3 - 70% to 84%
4 - > 85%


2. Play with this and let me know. Run the macro CalculateRubric by entering Alt-F8 to get the run macro list

3. ALso check the calculations just to be sure

[elsone31]

Paul,

Some of the calculations are not coming out right. One reason is because I failed to include +G, +F, +H and +J as possible correct answers that need to be converted to a 1. I located that part in the code and was able to change it. Also, any numeric answer with a + sign in front of it needed to also be changed to a 1 and all other numeric numbers would need to be changed to 0's.

Also, any "*" need to be changed to a blank and not included in the denominator for the student.

This. IS. AMAZING. I did this with formulas and had like 6 new sheets, the fact that this creates just one new sheet is awesome.

Erica

[Paul_Hossler]

So you're OK with it now and comfortable updating the macro as your needs change?

If you want me to revise the macros, just ask.

[elsone31]

Paul,

Yes, please, can you update the code to:

1. Replace "*" as blank, and
2. Convert the numeric answers to 1's or zeros, as it is now it is converting the correct answers to 0's and the incorrect answers are blank which means they are not counting in the student's data.

I was to change the other part because it was OBVIOUS.

Thanks,
Paul

[Paul_Hossler]

#1 is easy enough, but

#2 is ambigious "Convert the numeric answers to 1's or zeros, as it is ... " -- what's the rules for making a numeric into a 1 or 0??? The range of numbers is pretty large (picture)

13968

[elsone31]

The numbers with the plus sign in front are the correct (1's) responses, all others will be incorrect (0's). Also, I ran the macro on a larger data set of about 3300 records, it ran beautifully. I so appreciate all you work on this.

Erica

[Paul_Hossler]

Hmmmmm -- this is what I think your data transform rules are

I could simplify the logic since the rules were revised / expanded / clarified :devil2:

No biggie - happens all the time. SW development is always an iterative process

Only things I might need to point out

1. +A, +B, ... +anything get marked as TRUE, so that they a NOT considered numbers in the next steps
2. To replace an actual * , you need to 'escape' it with a ~. Dashses are OK
3. Remaining text (A, B, C, ..., Zebra) becomes a 0
4. Remaining numbers (12, 34, 56, ...) becomes a 0 since things like +60 were really text and the + caused it to be caught in step 1 and marked TRUE
5. Last the TRUE from step 1 are selected and changed to 1




Private Sub ReplaceResponse()
Dim rCell As Range
With rResponses

'replace +anything with boolean True as special marker temporially
Call .Replace("+*", True, xlWhole)

'clear dash and asterick
Call .Replace("-", vbNullString, xlWhole)
Call .Replace("~*", vbNullString, xlWhole)
'any remaining numbers (123, 456, 789) go to 0
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlNumbers).Value = 0

'any remain text (A B, C, etc.) to to 0
.SpecialCells(xlCellTypeConstants, xlTextValues).Value = 0


'now make the True from above back to 1
.SpecialCells(xlCellTypeConstants, xlLogical).Value = 1
On Error GoTo 0
End With
End Sub

[elsone31]

Perfect, perfect, perfect! I can not thank you enough Paul.

Erica

[Paul_Hossler]

No problem -- it was an interesting exercise

Hope now that you've dipped a toe into the VBA waters, you can maintain a lot of it yourself.

It should be a fun project for you also, but you can always come back and ask if you get stuck

[elsone31]

It is my plan to definitely get stronger in VBA. You have helped me so much, but would it be possible for you to explain the different parts of the code for me, like in your reply yesterday with more details about what is is doing? How you set your dims and so forth? Define vocabulary for me like boolean, call just in general how you were able to take each part of my needs and get it to the final product.

Thanks!

[Paul_Hossler]

It would probably be much more efficient for if you got a book on VBA and worked through that (even one of those overly cutsie 'Dummies' books)

We're always glad to answer questions, but it'd be very hit or miss trying to do extensive tutoring (might be the wrong word??) in this format since I know I'd forget and skip over important material

[elsone31]

Yes, of course, I understand. I will indeed do that.

Erica