+ Reply to Thread
Results 1 to 8 of 8

Split text from userform across multiple cells

  1. #1
    Registered User
    Join Date
    12-31-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    4

    Question Split text from userform across multiple cells

    Split text from userform across multiple cells

    Hello,

    I am new to Userform. I didn't even know Userfom 2 days ago. So my knowledge is VERY limited and I was hoping you guys will help me figure this out.

    Basically, we will get some data (Product IDs and categories) from our clients and we need to link them.

    Country Product ID Category Category IDs
    US 1234567890 Books
    1234567897 Electronics
    1234567898 Accessories
    1234567899 Clothing
    1234667891 Food


    Categories have unique IDs and I will be using VLOOKUP to look for their unique IDs under column "Category IDs" (which are country dependent, therefore country is necessary)

    However, most of the time data is quite large (say 100 at a time). To add each product ID and their category is quite time consuming. In the userform, I wil select the country,Copy and paste all the product IDs & Order IDs in the respective fields.

    So what I am hoping to achieve is when I click Submit, I would prefer it the product IDs be split (one product ID in each row) instead of all product IDs in a single row.

    So I will have two columns with product IDs and orders IDs. I can then use AND function to make their combinations and then feed it to an app we use which will link them easily. This will save a lot of time.

    As I am unsure how I can attach the sample file, I uploaded it to my Google drive:

    https://drive.google.com/file/d/1nXq...ew?usp=sharing

    Thank you in advance for helping me out.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Split text from userform across multiple cells

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Split text from userform across multiple cells

    Placing all that data in 1 cell is not a good idea as you would not be able to do anything with the data.

  4. #4
    Registered User
    Join Date
    12-31-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    4

    Re: Split text from userform across multiple cells

    Quote Originally Posted by davesexcel View Post
    Placing all that data in 1 cell is not a good idea as you would not be able to do anything with the data.
    Thank you for the quick response. No, I don't want to place all the values in a single data. I want to split the values (1 product ID in each cell) but unable to. I tried attaching a sample file but the file browser pop-up isn't working for some reason. Hence google drive.

    See the image at below link:
    https://drive.google.com/file/d/0B1-...ew?usp=sharing

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Split text from userform across multiple cells

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Registered User
    Join Date
    12-31-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    4

    Re: Split text from userform across multiple cells

    Thank you very much. Worked perfectly.

    As for using "End with" instead of "Select, Selection and Activate" I actually have no idea how they work. I got that from internet search and trial-error. Would you explain the difference? I am okay with it as is but if it causes any errors, I would try to change them as you suggest.

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Split text from userform across multiple cells

    You do not need to select or activate a worksheet for writing values.
    That way you also avoid flashing of the screen while executing code

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-31-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    4

    Re: Split text from userform across multiple cells

    Quote Originally Posted by bakerman2 View Post
    You do not need to select or activate a worksheet for writing values.
    That way you also avoid flashing of the screen while executing code

    Please Login or Register  to view this content.
    Hello,

    Thank you for the information. My workbook will be having multiple sheets and the sheet will probably named something. Will that not be impacted if I remove the activate sheet? I just want to make sure the values are pasted correctly.

    Also, with your help and internet, I am almost done with my project except one thing which I am yet to resolve.

    I have set-up 3 user-forms now:

    1) 1st one will allow entering the data. Clicking OK will copy the date to sheet and also bring 2nd userform
    2) I have a slight problem with 2nd one:

    2nd userform will show values from 2 columns (D & E). E column of those two will show values only if the data from 1st userform is 100% accurate. Slight mistakes means error with vlookup and cell will return blank. And our clients keep doing such mistakes regularly.

    If such an error happens and a cell in E column is blank, corresponding D cell will show a hyperlink (for a webpage to that category, with-in office network. The website can handle slight mistakes). Webpage will show the correct ID for the category which user can enter in E column in 2nd user-form and proceed to 3rd user-form.

    The issue is that the hyperlinks, from column D, when displayed in User-form are converted to normal text without any formatting and I won't be able to click on them to visit the webpage. Copy and paste each site is also not the option as I use friendly name with the HYPERLINK function (webpages are too long with many parameters)

    Is there anyway you can help me with this?

    Also, would be glad if you can point out any other mistakes I am doing (like Activate sheet). This is almost done.

    Updated Sample:

    https://drive.google.com/file/d/1lk1...ew?usp=sharing


    Code I am using is:

    Please Login or Register  to view this content.

+ 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] create a vba function to split text in multiple non adjacent cells
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2017, 01:02 PM
  2. Create udf to split text in multiple non adjacent cells
    By leprince2007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-15-2017, 12:01 PM
  3. [SOLVED] How to split text & numbers in one cell to multiple cells
    By MaheshK5277 in forum Excel General
    Replies: 11
    Last Post: 02-23-2016, 07:13 AM
  4. Replies: 4
    Last Post: 11-11-2015, 03:00 PM
  5. Split text in single cells to be in multiple cells
    By mason0567 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2015, 02:59 PM
  6. Split a long text in a userform textbox to multiple rows on a worksheet
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2012, 09:03 AM
  7. split text in one cell into multiple cells without breaking the wo
    By Prashant in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2006, 04:48 AM

Tags for this Thread

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