+ Reply to Thread
Results 1 to 17 of 17

offsetting text from blank cells

  1. #1
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    offsetting text from blank cells

    Hi,

    I have used the below code previously and I am trying to repurpose it for something else. I would like to offset the text in cells B10, B13, B14, B15 if the associated cell in column C is blank. is this possible in the below code?


    Please Login or Register  to view this content.
    Many thanks in advance

  2. #2
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    any suggestions would be gratefully received.

    MTIA

  3. #3
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Hi, I have added in an 'offset' which has provided the Address for column A; however, i still need it to a convert it to a text value

    Please Login or Register  to view this content.
    I have attached a sample workbook to illustrate what I need.

    many thanks
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Hi, does anyone reply to say if something cannot be done? I would just like to know whether I need to explore other options.

    Many thanks.

  5. #5
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: offsetting text from blank cells

    It frustrating when no one answers, but that's usually because no one understands what you're after: I can't tell from you posts or attachments.

    When the button is clicked:

    What do you want it to check for?
    What should it do if that condition is not met?
    And what should it do when it is?

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

    Re: offsetting text from blank cells

    And where does Column C come in like you state in your opening post ?
    Maybe this is what your after ?
    Please Login or Register  to view this content.
    Last edited by bakerman2; 06-08-2016 at 08:31 PM.
    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.

  7. #7
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Thank you for your response and I apologise for not making it clearer.

    The original workbook I am working with has hundreds of cells where data is entered.

    before they save and send the workbook for approval (via another command button), I need a messagebox telling them what cells are missing data so they can only send it once completed. so the answer to your questions are as follows:

    What do you want it to check for? - missing information in series of cells
    What should it do if that condition is not met? message box with the 'name' of the cell they have not entered, not just the cell reference
    And what should it do when it is? - exit sub

    I can see that bakerman2 has also responded to this below.

    Best Regards.

  8. #8
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Thank you Bakerman2 also for your response.

    I have just tested your code on my test workbook and that is exactly what I was looking for. If I wanted to adapt it so it looked for a series of individual missing cells in a range rather than a 'range' ie A1:A5 but A1, A7, A9, A15 etc, would that also be possible? so a random selection as originally mentioned in the first post.

    the test workbook was for illustrative purposes only.

    Many thanks in advance.

  9. #9
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Thank you Bakerman2 also for your response.

    I have just tested your code on my test workbook and that is exactly what I was looking for. If I wanted to adapt it so it looked for a series of individual missing cells in a range rather than a 'range' ie A1:A5 but A1, A7, A9, A15 etc, would that also be possible? so a random selection as originally mentioned in the first post.

    the test workbook was for illustrative purposes only.

    Many thanks in advance.

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

    Re: offsetting text from blank cells

    To provide a suitable solution we would have to know exactly what cells we're talking about.

  11. #11
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Dear Bakerman2,

    Many thanks for your response. I really appreciate your assistance.

    I have attached a workbook and put an X in the Cells where I need the corresponding 'Text' some are in column A, some are in Row 20.

    it is A8, A11, A12, A13

    C20, H20, I20, J20, L20, N20, O20, P20.


    I realise this is a lot to ask and so if it is too complex, then please let me know.

    Many thanks once again.
    Attached Files Attached Files

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

    Re: offsetting text from blank cells

    Test example file.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    many thanks for this and apologies for the delay in response.

    i have tested this and it is great so i will simply tweak one or two bits.

    Many thanks again.

  14. #14
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    Hi,

    Can you please help me with one last point...if the cells indicated below are all NOT blank, ie the form has been completed successfully, then how do I get the code to move onto the rest of the sub?
    I have included the first few lines of the rest of the sub at the bottom

    Many thanks in advance.

  15. #15
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    for some reason, couldn't post the code so I have attached in notepad
    Attached Files Attached Files

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

    Re: offsetting text from blank cells

    Made an example file that you can tweak to your satisfaction.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-20-2012
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: offsetting text from blank cells

    That's great. Many thanks once again.

    I am experiencing some slight strange behaviour with the code but I will try to work it out.

    Many thanks and best regards

+ 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. Offsetting with merged cells
    By noobsesher in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2015, 07:04 PM
  2. [SOLVED] Combine multiple cells text into one cell, skip blank cells.
    By T15K in forum Excel General
    Replies: 6
    Last Post: 12-05-2014, 09:49 PM
  3. [SOLVED] Cocatenate Range of Cells in a column, remove blank cells, and stack text in a cell
    By paxile2k in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2014, 07:47 PM
  4. Function returning cells containing text and omitting blank cells.
    By reszax in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2013, 03:15 PM
  5. Cell wrap text: Some cells have blank white space above text
    By stuckagain in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 06-09-2009, 06:47 AM
  6. offsetting values of multiple cells!
    By via135 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-18-2006, 01:20 AM
  7. Offsetting Cells
    By PGalla06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2005, 07:05 PM

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