Posts by GreasySpot

    Re: Put multi-line textbox into multiple cells


    Thanks for the quick reply and I do follow most of it.
    I assume this


    "You need to find out what NewLine character is used. This can be Chr(10, Chr(13) or Chr(10) & Chr(13)"


    and


    "Split(TextBox_Gross.Text, vbCr) ' OR vbLf or vbCrLf"


    are what you are referring to. I'm just not sure how I'm supposed to check that?


    If I put in a value of


    123
    456
    789


    in the inputbox the watch window shows a value of "123 456 789", but when I click on the value in the watch window it changes to "123456789". Also I noticed it lists it as a variant/string, I will be needing it as a number and not text.


    I apologize for being so lost.

    I'm not sure how to phrase my question so that everyone understands. I want to take each line of a multi-input textbox and put each line into its own cell. Notice the form below. Each item in "Gross Weights" needs to be paired with all the other items to form the rows on the left. My code for this is below but it doesn't separate the items. It just creates a multi line cell.



    [ATTACH=CONFIG]68762[/ATTACH]



    Re: Next cell with data?


    I was able to replace ThisWorkbook with ActiveWorkbook and it seems to work fine.


    I was testing it on some random data though and it was picking some columns. Or it jumped to the first item but not the next item. Some columns it wouldn't jump to any items.
    [ATTACH=CONFIG]68170[/ATTACH]

    Re: Next cell with data?


    WOOHOO! I finally got it to work. I do have one small request though (I think it's small anyway).


    I store most of my code in a "Personal" file so any open file can have access to it. This portion of your code "Set ws1 = ThisWorkbook.ActiveSheet" references the file where the code is stored which is empty. Is there an easy way to change "ThisWorkbook" to maybe the active workbook or something?


    Other than that it works like a charm!


    Thank You! Thank You! Thank You!
    Will

    Re: Next cell with data?


    I did some digging around and I found this code that will return the last row used.


    However I would not want it only for sheet 1. Just thought this might help you.

    Re: Next cell with data?


    Quote from gijsmo;764515
    Code
    With ws1.UsedRange
        'find a spot to place a temporary formula
        lRight = .Columns.Count + 1
        'work out the last row
        lEnd = ws1.UsedRange.Rows.Count
        'make sure there is a row left to jump to
       [B][COLOR=#FF0000]If lTop > lEnd Then Exit Sub[/COLOR][/B]
      End With


    It ends after the section in red. lTop is always 1 more than the current row number and lEnd is always 1.

    Re: Next cell with data?


    Ahhh.....I see said the blind man. My sheet is generated from another program and it is putting in 0's where there "APPEAR" to be blanks. I never realized it did that. My apologies if I ruffled any feathers previously.


    Now that you have sorted that out can it be modified to find the next value greater than or less than 0 and jump to it?


    Thanks in advance.

    Re: Next cell with data?


    Quote from gijsmo;764484

    The code starts from the current active cell, eg column F row 5 and will then jump down to the next non-empty cell in the column until it gets to the last cell in the column.


    Yes it does this but if there is nothing in column F until row 20 it stops on row 10 because there is something in A10. I only want it to look in the current cells column.

    Re: Next cell with data?


    Let me try to explain a little better. If my cursor is in cell F5 I would like it to jump down column F to the next cell with data in column F, whether it be F6 or all the way down to F100. It doesn't matter which column it just needs to be the active cell column. Does that make more sense? If someone could point me in a direction I will try to work it out.


    Thanks in advance.

    Re: Next cell with data?


    gijsmo, Thanks for the help. It sort of works in that it will jump to the next row with data in however I only want it to look in the current column, not the other columns. Does that make sense?


    Thanks again

    I know this may seem easy but for some reason it's not for me. I have looked around many sites with many scenarios and many solutions, but nothing like mine. My excel works perfectly. It opens correctly when double clicking a file. No problems there. I didn't like Excel opening to the "startup screen" so I tried to change it and this is where I am now. When I double click on the Excel icon it opens to a normal looking window with the ribbon and everything; however the ribbon is greyed out and "spreadsheet" portion of the screen is greyed out, no lines or cells or anything. What I would like it to do is open to the blank worksheet when I double click on the Excel icon. Easy right? Thanks in advance for any and all help.


    Will