Search Search for: Business ApplicationsAll Forums

# Solved: Changing all numbers in excel?

Member with 273 posts.

Join Date: Aug 2006
27-Dec-2008, 05:44 PM #1
Solved: Changing all numbers in excel?
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?
Member with 4,530 posts.

Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
27-Dec-2008, 05:55 PM #2
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

Last edited by Rollin_Again; 27-Dec-2008 at 06:20 PM..
Member with 273 posts.

Join Date: Aug 2006
27-Dec-2008, 06:12 PM #3
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
Member with 4,530 posts.

Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
27-Dec-2008, 06:23 PM #4
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
Member with 273 posts.

Join Date: Aug 2006
27-Dec-2008, 06:28 PM #5
Thank you
Member with 4,530 posts.

Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
27-Dec-2008, 06:38 PM #6
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

Code:
```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
Attached Files
 ConvertNumbers.xls (24.0 KB, 150 views)
Member with 273 posts.

Join Date: Aug 2006
27-Dec-2008, 06:56 PM #7
Thank you I will mess around with that in the morning and hopefully get it sorted. Thanks again!
Member with 273 posts.

Join Date: Aug 2006
29-Dec-2008, 07:14 PM #8
Thank you Rollin_Again your macro worked perfectly
 techguy.org/783903
As Seen On

WELCOME TO TECH SUPPORT GUY!

If you're not already familiar with forums, watch our Welcome Guide to get started.

 Tags convert, excel, formula, numbers, range

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)