[ajrobson]

Can anybody help with this question? I have a massive spread sheet with numerical data in it. on paper I have associated a number to different ranges.

For example in columb A any number that is between 25-30 needs to be converted to 0.375. Can anyone tell me a quick way to convert these and other numbers in one go?

[Rollin_Again]

You will need a macro to do this. I can write it for you if you can post a sample workbook with detailed instructions on what you want. If you prefer not to post it in the forum you can email to Rollin_Again at hotmail dot com

Another option is to use a helper column with a formula consisting of a series of nested IF functions. The macro is probably a better option since there is a limit to the number of nested IF functions you can use.

Regards,
Rollin

[ajrobson]

There is quite a bit I still need to do, I assume if I saw one macro I could chnage the numbers to work with the others?

If you can could you write a macro that would look at all the numbers in columb B (which are betwwen 15.17-76.75) and chnage them on this criteria

Between Chnage to
15- 17... 0
18-20... 0.125
21-23... 0.25
24-29... 0.375
30-33... 0.5
34-43... 0.625
44-59... 0.75
60-70... 0.875
71+ ... 1

[Rollin_Again]

Give me a few minutes to put some basic code together. It would probably be best to put the new values in a "helper" column instead of changing the original data so that you can compare the values to verify that the macro processed the data correctly. You can then just delete the original column and keep the "helper" column instead.

Regards,
Rollin

[ajrobson]

Thank you

[Rollin_Again]

Here is the macro code. It should be pretty easy for you to understand. I've also included a sample workbook so you can see the code in action. Just save the workbook and then run the macro called ConvertNumbers

Sub ConvertNumbers()

Dim vRange As Range

'Set your range of cells to process
'You can also set the range dynamically using code 
Set vRange = Range("B1:B50")

For Each vValue In vRange

Select Case vValue

Case 15 To 17
vNewValue = 0

Case 18 To 20
vNewValue = 0.125

Case 21 To 23
vNewValue = 0.25

Case 24 To 29
vNewValue = 0.375

Case 30 To 33
vNewValue = 0.5

Case 34 To 43
vNewValue = 0.625

Case 44 To 59
vNewValue = 0.75

Case 60 To 70
vNewValue = 0.875

Case Is > 71
vNewValue = 1

End Select

'Place new value in adjacent cell - column C
'If you want to replace the original value change to column B
Range("C" & vValue.Row).Value = vNewValue

Next vValue

End Sub

Regards,
Rollin

[ajrobson]

Thank you I will mess around with that in the morning and hopefully get it sorted. Thanks again!

[ajrobson]

Thank you Rollin_Again your macro worked perfectly