Posts by Pesky Weasel

    Re: Networkdays Function


    Rob,


    This should do it.


    NB: dates are inclusive, change function name to something less wordy that will be relevant to you.


    Regards
    Weasel


    Re: vba conditional action


    Difficult to tell with only some of code and no real idea of the desired end result. See below - you will need to edit the End If statements depending on the desired result. As the offset enumeration is dependant on the range given by rngimport it should find the cell 8 spaces to the right of the cell referred to by this variable each time.


    Post back if this doesnt have the desired effect.


    Regards
    Weasel


    Re: vba conditional action


    adncmm,


    How about:


    Code
    if rngcheque = 0 then
    'yourcode
    Else
    'your other code
    End If



    NB: use 0 to detect a cell that contains a zero value, use "" to detect an empty cell


    Regards
    Weasel

    Re: Disappearing paste>?


    While not being overly sure what the end result should be, the paste section is still working fine, assuming that is that the delete procedure doesnt end up deleing the data in E3:Y4.


    Regards
    Weasel

    Re: Disappearing paste>?


    Works fine for me. Are there any other procedures run after this - in particular I would be looking for a sheet level procedure being run on the sheet change event for the target sheet?


    Regards
    Weasel

    Re: find vendor name on customer list


    acctlab,


    A little hard to give a firm answer without seeing a better snapshot of your data, but in short - it is hard to match data if the two data sources dont match. What your are asking is for excel to make a guess as to whether they refer to the same company name or not.


    For a text comparison:


    'abc company, inc' = 'abc co.'


    is just as false as


    'abc company, inc' = 'special fried rice'


    Regards
    Weasel

    Re: Is there a way to show more tabs?


    Wrecking Crew,


    AFAIK there is no way to have a split level sheet tabs bar (the same effect as increasing the height of the Windows Task Bar). You could build a custom control to place in the right click menu for the sheet or in a custom toolbar but this would be a little redundant as the option is available by right clicking on the |< < > >| arrows to the immediate left of the sheet tabs.


    Regards
    Weasel

    Re: Show UserForm upon Opening Workbook


    mileseve,


    Not sure what you mean by inactive. By default you are unable to use a worksheet while a userform is displayed - unless you are using a modeless userform.


    If you want your worksheets hidden while the userform is displayed (remembering that at least one must be displayed - this would typically be the splash screen) try using worksheet visible in the inialize/terminate events of the userform.


    eg. in the code section of the userform:


    Code
    Private Sub UserForm_Initialize()
    Sheets("sheet1").Visible = 0
    Sheets("sheet2").Visible = 0
    End Sub
    
    
    Private Sub UserForm_Terminate()
    Sheets("sheet1").Visible = 1
    Sheets("sheet2").Visible = 1
    End Sub


    Just be sure to use


    Code
    Unload userform1
    
    
    NOT
    
    
    userform1.hide


    to make the form go away.


    Regards
    Weasel

    Re: Detect new datas


    No need to select the range.


    just goto insert - Name - Define.


    Type the name of the range in the 'Names in Workbook' box and type the formula in the 'Refers to Box'


    Regards
    Weasel

    Re: range vs dynamic range


    booger,


    Thats the nuts & bolts of it, yes.


    eg.


    Named Range


    myRange = A1:B5


    will refer only to that range.


    Dynamic Range


    myRange = Offset($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))


    will start at A1 and will go down as many rows as have data in in column A and across as many columns as have data in Row 1. As you add contiguous columns and rows the range expands to include them.


    Regards
    Weasel

    Re: ListBox 2 column additem


    Dave,


    In this example I have a list box with 5 columns. The values for each column are filled by a1:e1


    Code
    ListBox1.AddItem range("a1").value
    ListBox1.List(1, 1) = range("b1").value
    ListBox1.List(1, 2) = range("c1").value
    ListBox1.List(1, 3) = range("d1").value
    ListBox1.List(1, 4) = range("e1").value


    Hope that helps
    Weasel


    to make this truly useful you should be looping through data to obtain a dataset of entries. You need to post more info to get more help with that.

    Re: database transfer


    modi,


    If it is a 3rd party app doing the exporting then you are probably in a little trouble.


    A copy of the excel report you have posted to the board would help, it may be possible to run code on the reports to sort them out..


    Regards
    Weasel


    I am off to bed now - try to get back to you tomorrow.

    Re: Button to increment cell number


    The internet is full of great websites that provide help with VBA - but dont go to far - you're already at the best.


    Check out some of the tips and tricks on the mothersite - here as well as posts on the board.


    Also have a look in the bookshop, there are some great learner resources there.


    Another good way to pick up syntax is record macros and examine the (inefficient but accurate) code generated, then play with it till it does what you want. This isn't possible 100% of the time but is great way to learn.


    Regards
    Weasel

    Re: Show UserForm upon Opening Workbook


    mileseve1,


    As Dave has said - hiding the workbook would be a bad way to go.


    However there are plenty of options around using Splash Screens - where only certain sheets are hidden, not the entire application. Do a search on board for Splash Screens and see what comes up.


    Regards
    Weasel