Macro/VBA If-then question

[Beachcomber1224]

Hello,

I am a beginner macros/VBA and have a question for how to set up a a project I am working on. I would greatly appreciate anyone's input in how to create a macro for this.

I want to enter a set of "original scores" from a form into Excel (for example - 1, 2, 3) and set up a macros that will then convert these "original scores" into raw scores (for example 10, 20, 30). My thought was to have the "raw scores" in the same column beneath the "original scores".

Does anyone have any suggestions?

Thank you.

 

[shadow12345]

You mean something like this? (Just the spreadsheet... i am assuming the raw scores are 10 times the orginial scores?)


<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">1 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">3 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=OFFSET(A2,-1,0)*10 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=OFFSET(B2,-1,0)*10 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=OFFSET(C2,-1,0)*10 </font></td></tr>
</table>

 

[Beachcomber1224]

Hi shadow12345,

Thank you very much for your reply/help. That is not exactly what I was hoping to do, my example wasn't very clear. Also, after more thought, I need to modify my question (sorry for any confusion).

Here's what I am thinking of:
I will have a number of values in the first part of the spreadsheet. Below them , I will need to have cells that convert these numbers to specific values. For example,
- If 5 in cell C2, put in 32 in cell C44
- If 14 in cell C2, put 27 in cell C44
- If 23 in cell C2, put 4 in cell C44

Is this any clearer? Thank you for any help that you are able to provide.

 

[poolhall]

If you need a macro:

Sub test
Select Case Sheet1.Range("C2").Value
     Case 5
          Sheet1.Range("C44").Value = 32
     Case 14
          Sheet1.Range("C44").Value = 27
     Case 23
          Sheet1.Range("C44").Value = 4
End Select
End Sub

 

[Beachcomber1224]

Hi Poolhall,

Thank you for your help - this seems like what I want to do. However, when I enter this into VBE, it does not run the macros. I am pretty sure this is my inexperience, but there may be a few steps that I'm missing.

Could you tell me exactly how to run this script?

Much appreciated

 

[poolhall]

This macro should be in a regular module. Once you placed it there, press Alt+F8 in Excel, select macro name, press Run.

Instead of macro you can use this formula in C44:

=IF(C2=5,32,IF(C2=14,27,IF(C2=23,4)))

If there's anything else but 5, 14 or 23 in C2, it will show FALSE.

 

[Beachcomber1224]

I have a follow-up to my previous question. With help from poolhall (thanks!), I was able to come up with a script where it would convert the following:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1 (C3) à 0 (C44)<o></o>
2 (C3) à 1 (C44)<o></o>
3 (C3) à 2 (C44)<o></o>
4 (C3) à 3 (C44)<o></o>
Using this code:<o></o>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)<o></o>
Select Case Sheet1.Range("C3").Value<o></o>
Case 1 To 4<o></o>
Sheet1.Range("C44").Value = Sheet1.Range("C3").Value - 1<o></o>
Case Else<o></o>
Sheet1.Range("C44").ClearContents<o></o>
End Select<o></o>

End Sub
<o> </o>
For some of these cells, the values will be reversed (see example below)<o></o>
1 (C4) à 3 (C45)<o></o>
2 (C4) à 2 (C45)<o></o>
3 (C4) à 1 (C45)<o></o>
4 (C4) à 0 (C45)<o></o>
<o> </o>
*Could someone please help me to make a code for these reverse values?<o></o>
<o> </o>
The end result will be a column that has about 30 of the “original” scoring (i.e. 1=0, 2=1) and 30 of the “reverse” scoring (i.e. 1=3, 2=2) values for each column. Is there a code that will take what script I make for this column C example and continue it for every column in the Excel spreadsheet?<o></o>
<o> </o>
Thank you all for any help you are able to provide. <o></o>