Posts by IanDunnett

    Re: Referencing User Selected Workbooks


    It might be that although you've opened the workbook it is not yet active so you're previous line is not selecting a sheet wihtin the workbook you've just opened.


    Try either inserting a line to activate the workbook first;

    Code
    1. Workbooks(csvFN).Activate
    2. csvSheetName = ActiveSheet.Name
    3. Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")


    or accessing the active sheet in the workbook you've just opened;

    Code
    1. csvSheetName = Workbooks(csvFN).ActiveSheet.Name
    2. Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")


    Cheers,
    Ian

    Re: Referencing User Selected Workbooks


    I think by using the reference method you are it's best to use normal referencing rather than the R1C1 variety.


    Try changing the line where the error occurs to;

    Code
    1. Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")


    Hope that helps,
    Ian

    Re: Next Available Cell Row


    Corrected code below;


    Cheers,
    Ian

    Re: Fill First Blank Cell In Multiple Rows


    This should be a simple way of achieving what you're after;



    Hope that helps,
    Ian

    Re: Report Of Employees By Date, Holidays & Sick Days


    There's a slight error in the code working out the number of hours...it's using the date rather than the info on the employee row...it just so happens that the formula applied to a 2008 date = 8 hours but applied to a 2009 date = 9 hours which is where the error was coming from.


    Updated section below;

    Code
    1. x = x + 1
    2. AbRec(0, x) = KeyMap(1, d)
    3. AbRec(1, x) = Sheets("Data").Cells(1, b).Value
    4. AbRec(2, x) = Sheets("Data").Cells(1, b).Value
    5. AbRec(3, x) = Int(Right(Sheets("Data").Cells(EmpRow, b).Value, _
    6. Len(Sheets("Data").Cells(EmpRow, b).Value) - 1))
    7. AbRec(4, x) = d


    Cheers,
    Ian

    Re: Next Available Cell Row


    Thanks. It's a structure I use quite a lot when needing to select something from a set that isn't too big.


    It saves having to write a userform with a listbox...


    It can be applied to workbooks, sheets, items within a table, filtering options...anything really where you need to select one / multiple items from a set.

    Re: Next Available Cell Row


    I'm assuming the common part of the filename is 'stat' here;


    try something like;


    This will search thorugh all the open workbooks and let you select the one you want to copy from by using a messagebox to ask whichis the right one.


    Then just use 'WbkToCopyFrom' as the variable everytime you need to refer to the source workbook.


    Hope that's clear and helps,
    Ian

    Re: Next Available Cell Row


    Something like;


    Code
    1. Dim EmpRow As Range
    2. Dim PrinttoRow As Integer
    3. Set EmpRow = Workbooks("workbookname").Sheets("sheetname").Columns(1).Find("", _
    4. After:=Workbooks("workbookname").Sheets("sheetname").Cells(1, 1), LookAt:=xlWhole)
    5. PrinttoRow = EmpRow.Row


    You'll need to replace sheetname and workbookname with the name of the sheet and workbook you're searching on and this assumes the workbook is open and that the first blank in column A is where you want to start adding to.


    Cheers,
    Ian

    Re: Formula Too Big For Nesting


    If you wanted a quick fix to this just create an extra column on each sheet with a formula in that gives you Year and Month in the format YYYYMM then you could easily maintain the data you need with pivot tables...


    Formula would be (assuming row 2 with date in column D);
    =YEAR(D2)&TEXT(MONTH(D2),"00")


    Cheers,
    Ian

    Re: Macro To Add VLOOKUP Formula To lookup Another Workbook


    I think you just need to separate out the variable in the string...


    try;

    Code
    1. 'Search for comments last week from last week's file
    2. Workbooks(this1).Activate
    3. With Range("P2")
    4. Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select
    5. Selection.FormulaR1C1 = _
    6. "=VLOOKUP(RC[-8],'[" & that1 & "]Raw'!C9:C18,10,0)"
    7. Selection.Value = Selection.Value
    8. End With
    9. Windows(that1).Activate
    10. ActiveWorkbook.Close


    Hope that does it.
    Ian

    Re: Colour Textbox


    Assuming your userform is called userform1, the textbox is called textbox1 and the checkbox is called checkbox1 then you'll need something like this in the code section of the userform which will fire any time the checkbox value is changed;



    Using the Locked and Enabled properties you could also stop users from beinmg able to alter the contents of the textbox.


    Hope that's useful,
    Ian

    Re: Delete Rows That Dont Have Certain Text


    Hi Cameo,


    Try this, code notated to help explain;


    Cheers,
    Ian

    Re: Conditional Formatting For N/a Fields


    I would go for using the iserror formula...


    =IF(ISBLANK(A7),"",IF(ISERROR(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)),"Missing Account","Match Found"))


    Cheers,
    Ian



    ADDED BY ADMIN
    =VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)
    In 1 column (eg "B") and;
    =IF(ISNA(B1),"Missing Account","Match Found")
    Hide column B

    Re: Report Of Employees By Date, Holidays & Sick Days


    Hi MJB123,


    I've quickly knocked up something that should get you somewhere near where you need to be...


    The sheet (attached) now has a userform to select the employee to run the report for, a command button on the data sheet to start up the macro, a report template page to copy the format for each individual and a key sheet which relies on the following;


    All codes for possible absence types listed.
    All codes are only one letter long.
    The order of the codes is the same as they appear in the columns on the report.


    here's the code;


    and on the userform


    First it summarises each nonn-blank column for the employee into a data record, then it puts that on a fresh template.
    Finally it then scans thorugh what it has just done and combines any records on consecutive days of the same absence type.


    Hope that Helps!
    Feel free to let me know any issues


    Cheers,
    Ian

    Re: Oleobject, Trigger Event, Not Firing


    Sorry I obviously read your post a bit quickly last time and tried to come up with too simple a solution.


    I think the key here is to not delte the buttons at all. I would just create an array that after stripping out the numbers, instead of deleting them, just finds the first number not already taken and uses that as the name for the new command button...


    That way you don't get any name clashes and you don't have to delete any which is what is probably causing the link to the macro to break down.


    Something like;



    That's not tested so it may not work 100% but it should get you in the right direction.


    Cheers,
    Ian

    Re: Create Chart With Dynamic Named Ranges


    I think the problem is that you are Dim'ing rng and val as ranges, then declaring them as strings...


    As .XValues and .Values as members of the .Series object are variants they can be either a string or a range, but not both.


    Make sense?
    Ian

    Re: Change Caption Of Label In Form


    Hi ANdy,


    Maybe you need to send an example because I don't think I'm getting why this is a problem...



    You can change the caption of a label at anytime as long as you have loaded the userform the label sits on (you don't even need to hide the form to do so).


    But if you want it to change just use the same line of code, just before you show the form for the second time, e.g.



    Hope that's better.
    Ian

    Re: Oleobject, Trigger Event, Not Firing


    Hi There,


    I've encountered similar problems before.


    It's normally that the EnableEvents property isn't set to TRUE. Although this is the default for Excel the property can sometimes be upset by breaking your code halfway through or when your code hits an error.


    My way around it when I'm writing code is always to have a command button that fires;


    Code
    1. Application.EnableEvents = True


    Which I click before testing anything with triggers, and then I'm finished you can add the same line of code as the first line of code against all your command buttons in the workbook.


    That way you can guarantee, as long as the user has clicked a command button first, that the events should fire every time.


    I know that's not a specific solution to your problem but I hope it gets you in the right direction.


    Hope that helps,
    Ian

    Re: Change Caption Of Label In Form


    Hi Andy,


    This should be pretty easy...


    If you want to change the caption on a userform that the calendar control is inserted on (say the userform is called UserForm1) then use;


    Code
    1. UserForm1.Caption = "Whatever the title should be"


    The other way would be to insert a label in the userform and have that changing value (if Label was called Label1);


    Code
    1. UserForm1.Label1.Caption = "Whatever the title should be"


    Or you could have a msgbox pop up before the form to say which date they should enter...there's lots of ways...


    Hope that helps. Let me know if you need something more specific.
    Ian