How do I loop the following script

[Beachcomber1224]

Hello,

I have the following script, but am having trouble looping it. There are basically three parts that I have condensed. Does anyone have suggestions on looping this until there is nothing in next column's 138 cell? Below is the condensed version of my script. Thanks for any help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Sheet1.Range("B5").Value
Case 1 To 4
Sheet1.Range("B72").Value = Sheet1.Range("B5").Value - 1
Case Else
Sheet1.Range("B72").ClearContents
End Select
Select Case Sheet1.Range("B6").Value
Case 1 To 4
Sheet1.Range("B73").Value = Sheet1.Range("B6").Value - 1
Case Else
Sheet1.Range("B73").ClearContents
End Select
Select Case Sheet1.Range("B15").Value
Case 1
Sheet1.Range("B82").Value = 3
Case 2
Sheet1.Range("B82").Value = 2
Case 3
Sheet1.Range("B82").Value = 1
Case 4
Sheet1.Range("B82").Value = 0
Case Else
Sheet1.Range("B82").ClearContents
End Select
Select Case Sheet1.Range("B16").Value
Case 1
Sheet1.Range("B83").Value = 3
Case 2
Sheet1.Range("B83").Value = 2
Case 3
Sheet1.Range("B83").Value = 1
Case 4
Sheet1.Range("B83").Value = 0
Case Else
Sheet1.Range("B83").ClearContents
End Select

Range("B138").FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"
End Sub

 

[Beachcomber1224]

I can fully explain this and my goals to hopefully resolve any confusion.

I have people filling out something and need a script that can convert the numbers that people circled to ones "revised" ones. There are 2 ways that these can be scored:
1. regular way
- If someone circles 4 it will convert to 3 (4=3), circle 3 convert to 2 (3=2), 2=1, 1-0
- these are for rows 6, 8, 9, etc and correspond to certain questions

 Select Case Sheet1.Range("B6").Value
        Case 1 To 4
           Sheet1.Range("B73").Value = Sheet1.Range("B6").Value - 1
       Case Else
           Sheet1.Range("B73").ClearContents
   End Select

2. reverse way
- If someone circles 4 it will convert to 0 (4=), circle 3 convert to 1 (3=1), 2=2, 1-3
- these are for rows 7, 11, 15, etc and correspond to certain questions

Select Case Sheet1.Range("B7").Value
     Case 1
          Sheet1.Range("B74").Value = 3
     Case 2
          Sheet1.Range("B74").Value = 2
     Case 3
          Sheet1.Range("B74").Value = 1
     Case 4
          Sheet1.Range("B74").Value = 0
     Case Else
           Sheet1.Range("B74").ClearContents
End Select

There are over 50 questions and they are randomly assigned as regular and reversed scoring.

I then need to have the total converted scores added

Range("B138").FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"

Sous2817 - You are correct, I want to loop this script that is in column B for all other columns. Basically, each columns is the data from 1 person.

My goal for this post is to come up with a script that will loop the script that I created in column B into all columns. Does this help?

Thank you again for your assistance.