+ Reply to Thread
Results 1 to 19 of 19

VBA code to convert Survey numeric values to words

  1. #1
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    VBA code to convert Survey numeric values to words

    Dear All,

    I have responses from a survey entered as numeric values on sheet 1 (see attached file). I need a vba code that will transfer/copy the contents of sheet 1 to sheet2 and in addition, convert the numeric values of the responses into words - using the following conversion codes:

    1 = Strongly Agree
    2 = Agree
    3 = Neutral
    4 = Disagree

    There is also a summary column after Q21. The numeric responses for the summary column should be converted as follows:

    6 = Excellent
    7 = Best
    8 = Very Good
    9 = Good

    I wish to add that the respondents for any survey undertaken will never exceed 30 i.e. the respondents will never cover more than 30 rows counting from row 2 in sheet 1. There may be times when they might be less than 30 respondents for e.g. 10, 15 or 20. But they will never exceed 30, and therefore I will never use more than 30 rows from row 2 in sheet 1.

    With the numeric responses converted into words and placed in sheet 2, I wish to then use Sheet 2 for my result analysis.

    I have a baby knowledge of vba - although I am learning. I would like to automate the conversion of the responses, and should be very grateful for any help with this, please.

    Thanks all for your help.

    Newqueen
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA code to convert Survey numeric values to words

    You dont really need VBA for this. The following formula will do the conversion for you...
    sheet2
    B2=CHOOSE(SHEET1!B2,"Strongly Agree","Agree","Neutral","Disagree")
    copied down and across

    For the Summary, you could either just add the other 4 to that, or create a new CHOOSE using just the 4 new codes
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Thanks FDibbins for your kind response.

    The reason that I need a vba code for it is that at some point, the data in sheet (the numeric values) will be cleared. When each analysis is complete and the final result is printed, the entire data in sheet 1 will be cleared (using some vba to do that).

    Also, if I can, I would like to avoid the problem of copying down and across the 'CHOOSE' formula. I would like to have a vba code do the conversion by clicking a command button each time sheet 1 is populated with numeric values. I am thankful to you for your kind suggestion. But if I can get a vba code to do the conversion, I would prefer that, please.

    Thanks.

    Newqueen

  4. #4
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    I have tried the 'Choose' formula out of curiosity, and I find it somewhat cumbersome. The formula does not not come out accurate for the summary column. And when I drag it down and across and it picks up blank cells/rows, it produces '#VALUE' which makes the sheet a bit messy.

    Please, if anyone can help me with a vba code for it, I would appreciate it.

    Thanks everyone for any help.

    Newqueen

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    try
    Please Login or Register  to view this content.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA code to convert Survey numeric values to words

    If you want VBA, Im sure some other member can help you with that.

    As far as the accuracy is concerned, if you enter it correctly, it will return the correct answer.
    To error trap for blank cells, wrap the formula in =if(cell="","",formula)

  7. #7
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Thanks FDibbins. I appreciate your help.

    Jindon has kindly provided me with a very helpful vba code, and I am grateful to him too.

    Sorry for posting the wrong reputation for you. It was a mistake. I could not correct it by posting the correct reputation. Be rest assured that I appreciate all your help. Thanks.

    Newqueen
    Last edited by newqueen; 01-09-2018 at 05:09 PM.

  8. #8
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Hi Jindon,

    You are a STAR! Thanks very much for the vba code you provided. It converts, very accurately, the numeric data to the desired words/texts! There is just a little tweak that I need:

    As I indicated in my original Post, I would like the conversion to appear in Sheet2. The main numeric data is in Sheet1 and there is the next sheet - Sheet 2 where I would like your vba code to transfer the conversions onto. As explained in the Post, if it is possible, I would like the vba code to copy across (on to Sheet 2) both the 1st row in Sheet1 and also the Column A in Sheet1 - i.e. the names of the respondents as they appear in sheet1.

    In transferring both column A and Row 1 of Sheet 1 to Sheet 2, the code will then populate the cells in Sheet2 with the converted words/text. With the way it is now, the conversion is done in sheet1 - overwriting the numbers.

    When all the transfers/conversions are executed and placed in Sheet 2, I will like all the entries showing from Row 2 (Sheet1) all cleared/deleted - to make way for new respondents to be entered for subsequent analysis.

    The reason that I would prefer Row1 and column A of Sheet 1 to be replicated in Sheet2 is that my final analysis will be carried out using Sheet2, and I would need those information.

    I apologieze if this is asking for too much. I am hoping that you will help me further.

    Again, thanks very much for your kind help.

    Newqueen

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    Is this how you wanted?
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Dear Jindon,

    Again, thanks heaps for your kind help. Your revised code transfers/converts the data to Sheet 2 very accurately the way that I want it. There are a few problems that I would request you, please, to look into, and kindly make the necessary adjustments for me. They are as follows:

    1. In my post #8, I requested that your code should transfer both the 1st row and column A of Sheet 1 (and the converted data) over to Sheet 2. I no longer wish to transfer over row 1 of Sheet 1. I will manually create row 1 in sheet 2 which is identical to row 1 in sheet 1. It will therefore not be necessary to copy/transfer row 1 from sheet 1.

    What should be copied over should start from Row 2 (Sheet1) i.e. the names of the respondents in column A starting from row 2. It is no longer necessary to copy over row 1.

    2. I also realise that when the copying over/conversion to sheet 2 is made, everything else at the lover part of Sheet 2 is wiped out. This should not be the case, please. The reason is this:

    a) Rows 33 to 57 of sheet 2 are used for the main analysis of the survey responses. These rows contain Excel ‘counta’ and ‘countif’, etc formulae that will pick up the data transferred/converted from Sheet 1 into the top rows of Sheet 2. The said Excel formulae automatically effect the analysis the moment the top rows of Sheet 2 are populated by the transfer/conversion which your vba code does.

    b) However, if, when the transfer/conversion is effected, and all the cells at the lower part of Sheet 2 are blanked out, then the formulae in rows 33 to 57 will be lost, and the analysis can’t be done.

    c) Please, I suggest that the transfer/conversion should be restricted only to rows 2 to 31 of Sheet 2. Everything else on Sheet 2 from Rows 33 to 57 and beyond (i.e. the analysis formulae), should be left intact, after the transfer/conversion from sheet 1,

    d) The reason I have indicated that the transfer/conversion should be restricted only to rows 2 to 31 of Sheet2 is because, the highest number of responses that we can get for each batch of survey will be 30. We have provided that 30 responses is the highest number that we can allow for each batch to trigger a batch analysis.

    e) This means that we can have:

    5 responses (occupying rows 2 to 6)
    10 responses (occupying rows 2 to 11)
    15 responses (occupying rows 2 to 16)
    Etc. up to 30 responses (occupying rows 2 to 31)

    But we will never batch more than 30 responses at a time, hence the analysis formulae are housed starting from rows 33 to 57. I therefore need your kind help with the above, please.

    3. My next headache, later, will be how to make the Excel formula in row 33 to 57 to be ‘dynamic’ if that is possible.

    I have just sent you a PM, please.

    Thanks for your continued help and for giving me some of your valuable time.

    Newqueen
    Last edited by newqueen; 01-10-2018 at 08:45 PM.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    I see.
    So, no data from row(1) to row(31) in Sheet2 before the macro?

    If so,
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Hi Jindon,

    Thanks. I'm sorry if I did not provide enough clarification.

    Row 1 in Sheet 2 will contain some data i.e. Q1, Q2, Q3 etc. permanently - which I will have to create. Your macro does not need to transfer that row 1 over to Sheet2 from Sheet 1.

    Therefore, no data from Row (2) to Row (31) in Sheet 2 before the macro.

    Your macro should transfer and populate Row (2) to Row (31) in Sheet 2. The data from Row 33 all the way down on Sheet 2 should not be affected.

    Infact, with reference to my paragraph 3 in my Post #10 above in which I was contemplating on an 'Excel formula to make rows 33 to 57 to be dynamic', someone has just pointed out to me that I can achieve the desired outcome if I make the relevant columns in rows 2 to 31 of Sheet 2 as 'named dynamic ranges'. That way, whether the number of respondents are 5, 15 or 20 and up to a maximum of 30, the rows will expand automatically, and the formulas that appear below in Rows 33 to 57 will adjust accordingly to the number of respondents.

    Thanks very much for your continued help.

    Newqueen

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    Then try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Hi Jindon,

    I have tried the change you suggested I should make. It does not transfer row (1) from sheet 1. However, it does not populate all the relevant columns in Sheet 2. On Sheet 2, it populates only from Q1 to Q15 columns. The rest of the columns Q16 column to the ‘Summary’ column are blank.

    Could this be the result of the following line in the code:

    Please Login or Register  to view this content.
    As indicated above, Q16 column up to the ‘Summary’ column are not populated with the change you suggested.

    With the inclusion of the line in your code that transfers row (1) of Sheet 1 across to Sheet 2, everything works fine – all the columns are populated.

    If it can be adjusted not to transfer Row (1) to Sheet 2, - with all the relevant columns populated, that will be very good. But if that is going to be a bother, I can avoid making the suggested change to the first line of the code. I can live with the 1st row being transferred.

    I must say, in all honesty, that you have given me superb support, and I am very grateful to you.

    What remains for me is to explore how I can make rows 2 to 31/columns A to W dynamic named ranges, so the formulas in Rows 33 to 57 will produce accurate analysis irrespective of the number of respondents.

    Meanwhile, if you have any suggestions on this, please, I would appreciate your kind input.

    Again, thanks very much for all your help..

    Newqueen.
    Last edited by newqueen; 01-11-2018 at 01:35 AM.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    Then I need to see your workbook.

    OK for Dummy data/Header etc, but data structure/layouts should be identical to actual workbook.

  16. #16
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Hi Jindon,

    The file is the same as the one that is attached to my Post #1 of this thread.

    The only problem is that the one-line code that you advised me to change with in your post #13 above does not populate all the relevant columns in Sheet 2 (i.e. Q16 to Summary columns). All that I ask is that your previous code which accurately transferred/coverted everything over to Sheet 2, should leave out transferring Row 1 from Sheet 1. This can be corrected using my original file uploaded in Post #1.

    There is no difference between another copy of the file that I would upload. Your code has already resolved the issue concerning ensuring that the copying over to Sheet 2 should be restricted to Rows 2 to 31. That's working fine. It is just to leave out copying over Row 1.

    I am leaving out the 'dynamic range' issue for now. I can do with not incorporating that at the moment.

    Thanks and sorry for the troubles.

    Newqueen
    Last edited by newqueen; 01-11-2018 at 05:24 PM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    If the range is fixed, can you just specify the range like

    Please Login or Register  to view this content.
    If you change A2, the other one should be changed, too.

  18. #18
    Forum Contributor
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007
    Posts
    144

    Re: VBA code to convert Survey numeric values to words

    Thanks very much Jindon. Your suggestion in post #17 is exactly what I needed. That was very kind of you, and I appreciate your patience with me.

    Newqueen

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA code to convert Survey numeric values to words

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Convert a Numeric value to Words Without Vba
    By HaroonSid in forum Tips and Tutorials
    Replies: 22
    Last Post: 03-02-2022, 03:07 AM
  2. how to convert numeric into words
    By roofi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2017, 10:42 PM
  3. Numeric convert into words
    By shahzeb_sjr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2016, 10:37 AM
  4. [SOLVED] How to convert a numeric value into English words
    By tuhinmmr in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-26-2016, 10:29 PM
  5. [SOLVED] convert words to numeric value
    By sekharks in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-29-2013, 03:58 AM
  6. Convert words into a numeric value
    By matric in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-29-2012, 01:56 AM
  7. [SOLVED] convert numeric value to words, error msg!
    By HHTrang in forum Excel General
    Replies: 2
    Last Post: 04-13-2006, 03:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1