Posts by Static Cat

    Re: Taken taken minus breaks over midnight


    Hi,


    Thanks for having a look.
    This does me the correct figure for this job but the problem is it gives me 8hrs over midnight for ALL the jobs now, instead of just the ones that actually go past midnight.

    Re: Error Handling for VBA offset with validation in cell.


    Thanks for looking. I did try Application.EnableEvents false also with no success.
    It works as it stands but doesn't like it when I try to clearcontents after an invalid entry attempt (or if I need to just delete the entry), ie the cell is blank so it tries to clear contents.
    The reason I'd like the clear contents to work is that if as it stands an invalid entry is made, the cell is blank, but the user info has been populated anyway.

    Re: Error Handling for VBA offset with validation in cell.


    This is code I have. This bit of code works, I've managed to strip out the problem bit.
    This issue was, that when something is entered in column B (the validated cells), it should update 2 other columns with user info.
    Part of my original code had something to clear the 2 user info columns when B is deleted/blank. This bit was breaking it. I can't seem to get the clear contents to work if value in B is deleted (or blank). So I think it was breaking because when validation error occurred the cell was left blank.





    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("b30:b128"), .Cells) Is Nothing Then

    ActiveSheet.Unprotect

    With .Offset(0, 12)
    .NumberFormat = "dd mmm hh:mm"
    .Value = Now
    End With

    With .Offset(0, 11)
    .Value = Environ("USERNAME")
    End With

    With .Offset(0, 1)
    .Select
    End With

    ActiveSheet.Protect

    End If
    End With
    End Sub

    I have a spreadsheet in which when a value is entered into a cell there is some VBA code that moves the selection one cell right. This was so that we didn't have to change the default move down/right setting in Excel.
    The problem I'm having is that this is also a Data Validated cell and if the entry fails validation, I get the stop message I need, but it seems to clash with the move right VBA and causes the debug thing to come up.


    is there anyway I can improve the basic move right code to check it was a valid entry before trying to move.


    I hope this makes sense.


    Thanks for reading

    How can i use an excel formula to determine whether 1 value is a clear multiple of another?
    Example Below


    [TABLE="width: 403"]

    [tr]


    [td]

    Scale

    [/td]


    [td]

    10

    [/td]


    [td]

    10

    [/td]


    [td]

    20

    [/td]


    [td]

    25

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    Value

    [/td]


    [td]

    105

    [/td]


    [td]

    100

    [/td]


    [td]

    125

    [/td]


    [td]

    125

    [/td]


    [td]

    20

    [/td]


    [/tr]


    [tr]


    [td]

    Multiple?

    [/td]


    [td]

    No

    [/td]


    [td]

    Yes

    [/td]


    [td]

    No

    [/td]


    [td]

    Yes

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [/TABLE]



    So that the Multiple? cells are calculated automatically.

    Re: Multiple Vlookup to create a list from a table


    Thanks for the help guys.


    Herbds7,


    How did you get the tables to run? I use, although only just installed, 2010 and cannot get the tables to be like your screenshot.
    How did you make them refernece the date field?
    If i can get it to run as your screenshot that would be perfect, i'm still trying to get my head around 2010 though.

    Hi,


    I am trying to use vlookup to search some tables and from that create a list that i can then pivot from.
    forum.ozgrid.com/index.php?attachment/44502/


    I have attached an example of the type of data source i am using and the list i would like to generate from this data source.
    Note that my data source is much larger than the example but the format is the same.


    Could i use offset instead of vlookup?


    Many thanks for taking the trouble to look and help.

    Hi,


    I used the following code in Excel 2003 to update some sql queries, then the pivot tables that run from them.
    The probelm i am having is that we have switched to Excel 2007 and now the Queries do not automatically refresh using this code.
    What changes do i need to make for this to work in 2007?


    Re: Error 22008 when using excel query


    Hi,


    Thanks for the reply, i've already tried that.
    I think that the problem comes from the way Excel puts the data in the cell.
    If i type the date in yyyy-mm-dd format when i first run the query it works, but when i type this format into a cell Excel always seems to change it dd/mm/yy no matter how i have the cell format set.
    See picture for example, what i've typed in the cell A1 is as exactly it's displayed "2011-02-25" but as you can see in the box (next to fx) Excel seen to have changed it.
    [ATTACH=CONFIG]40966[/ATTACH]

    Hi,


    I'm trying to use a date parameter using an ODBC query in Excel.
    I want to be able to enter a data on my sheet and use it as a parameter in the query but i get ERROR 22008. I am able to use any other non-date parameters in non-date fields it's only get this error when trying to query dates.
    How can i overcome this when using an Excel database query?

    Hi,


    I'm using the following macro via a button to save a workbook and display in a cell the date it was last saved.
    What i would also like to see is in another cell the name of the user that last saved.


    Code is as follows:-



    Any help is appreciated, many thanks.

    Re: Adding 'Searching, please wait' type dialog to a query


    Ah no probs :) Thank you, i did wonder.


    I have also, and i should have thought of this first, added the following at the start:-
    Range("A14").Select
    ActiveCell.Value = "Please Wait - REFRESHING"


    and to the end:-
    Range("A14").Select
    ActiveCell.Value = ""


    So that it display the message in the sheet under my button.


    What yours does is perfect though as i can see it run through each sheet.


    Thanks again for your help