Posts by spnz

    Hi there.

    I am working on some code to create a more user friendly error handler.

    Part of my code is to display the name of the sub where the error occured.But I dont know how to do it.

    for example

    Can anyone help out?

    Thank you


    Re: Get Information From Active Directory

    Ok So I have managed to work out some code that returns all the users from AD.

    But this leads me to my next question.

    I use this to populate a combobox with all the users full names of our AD. How can I make it so once I have selected a user then that persons 1st Name & Surname populate 2 textboxes?

    Any suggestions would be really helpful!!

    This is the code.

    Thank u

    Hi everyone,

    I have a piece of code that gets information from AD.

    I am wanting some help with changing things around a little.

    The current code only returns 1 users details, how can I modify this code so it returns all the users on AD?

    When the current code runs into an error e.g. If I search for a user that doesn't have a First name added to the AD then I get an error


    The directory property cannot be found in the cache.

    Is it possible to change this so instead of getting an error saying this for me to get an error with the actual field that has caused the problem as this message doesn't mean to much once you have a number of different fields you are trying to get back from AD.

    This is my code

    Any help would be great!!

    Thank you

    Hi there,

    I have not have to much to do with XML and by the way Office 2007 is looking I am going to be needing to learn quickly.

    I am trying to make a ribbon in a word template using XML.

    I found this example on microsoft but it only contains 1 group. What do I need to do to add another group with a command button?

    This is my code.

    Thanks for your help!!

    Re: Change a reference

    Managed to sort it out myself.

    Just needed to reference each of the templates using the reference feature in the VBE

    Hi there,

    I am in the process of updating some 2000 templates into 2007.

    I have created the new .dotm file but some of the documents are still referencing the old 2000 documents.

    Does anyone know how I can update this to reference the new documents?

    Any ideas would be great

    Re: Find empty cell and add formula

    Thanks Andy that worked great.

    I just had to add a couple of extra lines to deal with any null values in column 5

    If Cells(lngRow, 5) = "" Then
                    Cells(lngRow, lngCol).Interior.Color = 16777215
                End If

    Thanks again for your help!

    Re: Find empty cell and add formula

    Thanks Andy,

    I am having a few troubles trying to achieve this.

    WHat should I change?

    I have tried using

    If Range("G$1<$D2") Then Interior.ColorIndex = 37
    If Range("G$1>$E2") Then Interior.ColorIndex = 37
        End With

    But I get a runtime error 1004. Method range of object Global failed.

    Thank you

    Re: Find empty cell and add formula

    Hi Andy,

    Yes they should be CF.

    There maybe a better way to do it but I use the formatting (which I have just checked and is wrong but that's another story)

    The start and end dates are very important as there should be no charges going through on any of those weeks. So what I have tried to do is apply CF to the dates where no charges should go through.
    Then the macro checks any of the cells that have been formatted and will place the value into the cell.

    I use this code to CF. Should I modify this so instead of CF'ing for it to change the actual cell colour?

    Re: Find empty cell and add formula

    Hi again Roy,

    I thought I had sorted this out but have only just come back onto working with the project.

    I have tried to modify your code slightly to work how I want it to.

    It works for some cells but not others. Is there anything sticking out like a sore thumb that I am not doing correctly?

    I have attached a small example.

    Thank you


    Re: Dynamic Advanced filter

    Managed to get it working.

    Didn't use either of your methods but I seem to have it working ok.....

    This is my code

    I do have another question.....With the code that I have created I insert the criteria I want to into B2000:B2001. Is it possible to use an array within my code instead of inserting the values into the spreadsheet before running the advanced filter? or am I best to leave it how it is.


    Re: Dynamic Advanced filter

    Hi Roy,

    I have checked the help and to be honest it doesn't help out at all.


    UsedRange Property

    Returns a Range object that represents the used range on the specified worksheet. Read-only.

    Do you have maybe a small example?

    Hi there.

    I am trying to create a function using vba and the advanced filter function.

    The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.

    This is my code.

    It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.

    I know how to find the last column and last row by using

    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        LastCol = Range("IV1").End(xlToLeft).Column

    But I don't know how to modify my code to use these.

    Can anyone advise what I should change?

    Thank you :)

    Good afternoon,

    I am hoping someone can help out me out with a problem I have.

    I am trying to create an advanced filter that filters my results into a row.



    would return

    Shane Tom Paul Mike

    I have tried changing this piece of code

    Range("M1:M300").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns("M:M"), CopyToRange:=Rows("25:25"), Unique:=True

    The problem is with


    I have tried using Range instead of Rows but I am not having any joy.

    Can someone suggest a different method?

    Re: Inserting a null value into a database

    Hi Shark,

    The spreadsheet is emailed to me on a weekly basis. I need the information in the database so I can pull a number of different queries.

    I am trying to automate this so I can create an add-in so anyone in my team will easily be able update our database.

    Good morning,

    Hope someone can help me out.

    I am trying to insert values into a access table.

    The spreadsheet where I am taking values from does contain blank cells. This is throwing out my code to insert the values into my table.

    This is my code.

    What do I need to do to enable inserting of null values?

    I have tried using Nz() but doesn't seem to work

    Name = Nz(Range("E" & b).Value, 0)

    Thanks for your help!