Scoring questions based on response in few columns

[thirstforvba]

Hi All,

I am working with an excel, which has data in several columns. Now I need to score columns 97 - 103. These columns include survey answer for a multiple choice question number 13. If a person responds to any answer between 97 to 100, it should be a score =4, if the response has column 101 or 102 included with others or as exclusive, then it should be scored 9. Response to column 103 is exclusive to begin with so anyone responding to 103 will not have a choice to answer 97 -101. So this scores 0 and this is fine. But I am having issues with developing macro for earlier 2 requirement. Irrespective the responders have answers in column 101 and 102, they are getting scored 4. I have tried multiple tweaks but I am just not getting it right. This is manual edit part of big huge fixed file which perfectly all the time except when I have twisted requests. I am not good with macros but just at basics.

This is what I have so far.

If StrComp(Sheet6.Cells(i, 103), "None") = 0 Then
Sheet2.Cells(i + 9, 7) = 0
Else
If StrComp(Sheet6.Cells(i, 97), "Phase I") = 0 Or StrComp(Sheet6.Cells(i, 98), "Phase II") = 0 Or StrComp(Sheet6.Cells(i, 99), "Phase III") = 0 Or StrComp(Sheet6.Cells(i, 100), "Phase IV") = 0 Then
Sheet2.Cells(i + 9, 7) = 4
Else
If StrComp(Sheet6.Cells(i, 97), "Phase I") = 0 Or StrComp(Sheet6.Cells(i, 98), "Phase II") = 0 Or StrComp(Sheet6.Cells(i, 99), "Phase III") = 0 Or StrComp(Sheet6.Cells(i, 100), "Phase IV") = 0 Or StrComp(Sheet6.Cells(i, 101), "Medical Device") = 0 Then
Sheet2.Cells(i + 9, 7) = 9
Else
If StrComp(Sheet6.Cells(i, 97), "Phase I") = 0 Or StrComp(Sheet6.Cells(i, 98), "Phase II") = 0 Or StrComp(Sheet6.Cells(i, 99), "Phase III") = 0 Or StrComp(Sheet6.Cells(i, 100), "Phase IV") = 0 Or StrComp(Sheet6.Cells(i, 102), "IVDR") = 0 Then
Sheet2.Cells(i + 9, 7) = 9
End If
End If
End If
End If

I have tried having medical device and IVDR in same line thus reducing second line for score 9, having them exclusively, adding 'And' rather than 'Or'. I am finally giving up. Can someone guide?

 

[thirstforvba]

I resolved this myself. Thank you.

 

[Peter_SSs]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.