1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Changing all numbers in excel?

Discussion in 'Business Applications' started by ajrobson, Dec 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    280
    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?
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  3. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    280
    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
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  5. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    280
    Thank you :)
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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:

  7. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    280
    Thank you I will mess around with that in the morning and hopefully get it sorted. Thanks again!
     
  8. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    280
    Thank you Rollin_Again your macro worked perfectly
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/783903