Posts by NoSparks

    OK, I had the file name screwed up when originally using DIR

    this should work for you

    I could get your checkboxes installed in the workbooks but couldn't stop the loop when using Dir and ended up with multiple checkboxes on top of each other in the cells.

    Adapted the late binding macro from this site and came up with this which seems to work.

    Adding to what jolivanes has indicated:

    "*" will find first non blank cell

    "" will find first blank cell

    But range.Find is a circular function that begins looking after the first cell in the stated range and circles back around to first cell, it being the last cell checked.

    So if the first cell in the range may be the first blank, but not the only blank, you'll need to specify the after cell.


    Set u6 = wksht1.Range("AA11:AA19").Find(What:="", After:=wksht1.Range("AA19"), SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues)

    In the description of the After parameter at the link provided. It says

    [SIZE=14px]The cell after which you want the search to begin...
    Notice that After must be a single cell in the range.
    Remember that the search begins after this cell;
    the specified cell isn't searched until the method wraps back around to this cell.
    If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.[/SIZE]

    Something in your parameter settings, likely LookAt, which is a persistent parameter, was last used to find xlPart and is now determining 11/1/YY is a match so the .Find never wraps back around to the first cell.


    On another workbook I have basically the same setup but starting at A2 with the same issue. It's almost like it doesn't actually see the 1/1/YY

    It doesn't sound like you tried changing the start of Set DateHeader from A3 to A2.
    Did you read the description of the After parameter at the link provided ?

    see if this alteration helps

    ''I have split it into 2 for trouble shooting purposes
        'MsgBox Hour(Sheets("Build Data").Range("O" & PasteRow).Value) & "hours and " & Minute(Sheets("Build Data").Range("O" & PasteRow).Value) & " minutes"
        MsgBox Int((Sheets("Build Data").Range("O" & PasteRow).Value) * 24) & " hours and " & Minute(Sheets("Build Data").Range("O" & PasteRow).Value) & " minutes"
        'MsgBox Hour(Sheets("Build Data").Range("N" & PasteRow).Value) & "hours and " & Minute(Sheets("Build Data").Range("N" & PasteRow).Value) & " minutes"
        MsgBox Int((Sheets("Build Data").Range("N" & PasteRow).Value) * 24) & " hours and " & Minute(Sheets("Build Data").Range("N" & PasteRow).Value) & " minutes"

    Have you tried using the full set of parameters of the Range.Find Method ?
    Note that some parameters are persistent and don't revert to a default value so unless you specify them you may not be using .Find the way you think.

    What happens if you change the start of the DateHeader range to A1 or A2 ?

    This line of Select_All()

    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

    results in 1048571 rows being selected for copy, and there aren't that many rows available to paste to.
    I tried this and it works. I wouldn't think the 5 blank rows being pasted at the bottom would matter.

    Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n

    Simplest way to 'scroll & continue to display all the items to the end' would be to create a user form (the shape of a message box) with a single listbox filling the entire form and show it rather than the message box. Populate the list box at initialization.

    Private Sub UserForm_Initialize()
    Me.ListBox1.List = Range("newvendor").Value
    End Sub