Reversing scores in a questionnaire

[Guest]

In school i have used a questionnaire with 29 questions. Every question is
given a score from 1-7. I need to reverse the scores of a couple of
questions. Meaning that 1 becomes 7, 2 becomes 6, 3 becomes 5, 4 is still 4,
and vice versa. How do i go about doing this? I need a program that
automatically changes the number when i enter it. Thanks
/Harald

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents = false
select case target.value
case 1
target.value = 7
case 2
target.value = 6
case 3
target.value = 5
case 5
target.value = 3
case 6
target.value = 2
case 7
target.value = 1
end select
application.enableevents = true
end sub

HTH

 

[Lonnie M.]

Do you really need to have a program to do this or will a worksheet
formula work? If not, how do you enter the data? through a text box on
a form or directly into the worksheet?
it is pretty simple to reverse the score: 8 - score
8 - 3 = 5
8 - 1 = 7
8 - 7 = 1
Worksheet formula where A1 = the score 3:
B1 formula "=8 - A1"

Let me know exactly what you are doing and more detail about what you
need if this didn't get you pointed in the right direction.
Regards--Lonnie M.

 

[Bob Phillips]

Harald,

Here is one way. Change the range to suit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value > 0 And .Value < 8 Then
.Value = 8 - .Value
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = 8 - Target.value
Application.EnableEvents = True
End Sub