Posts by error#9

    Re: Transfer Cells Values To Cell Comments

    Fencliff and JIUK, Thank you both so much!
    Fencliff / or anyone in RE to the .xla version, how do i get it NOT to put a comment if there is nothing in the cell, at the moment i need to copy large ranges but there is some blanks, and its creating a comment for a blank!

    any ideas?

    Re: Transfer Cells Values To Cell Comments

    That is absolute genious!!!
    I didnt have a clue even where to start!
    This is gonna save so much time..

    I wish there was a bit of code i've got to upload to help you guys out but i'm afraid my stuff maybe medioca!

    Again, well done and thank you SO much, this is a happy Error#9....... i love this website.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]One quick quick adjustment, how can i get it not to put a comment the the cell in the range is blank, sorry to be a pain :)

    Re: Paste Cells As Comments

    Ok, well i think will work best is somehow intergrating an


    so then what i want to do is when i've [CTRL+C] to copy the notes, is there a way to get this code to run of a custom menu on the 'Right Click' Menu, i've heard this can be done??

    Thanks again

    Re: Paste Cells As Comments

    This is bugging on a few things.

    On this line

    myTargetCell_COMMENT_TEXT = myTargetCell_COMMENT.Comment.Text

    it says


    Object or with method not found

    Can this run on the cells which has been 'Right Clicked' on?

    Hi all,

    Here is an interesting one.

    I have a report where, month in month out, i have to append columns on the right, to give a Year to date figure. Now there are many rows with different numbers but one of the columns is call [NOTES]. This is where the user can provide commentry on the weekly figures.

    Now whats getting annoying is i have to copy this commentry and then create a comments box then paste the text in there and finally, hide the comment.

    So what i want to be able to do is be able to Copy the cell, then, have a right click menu button saying "Paste As Comment", so it paste the selected cells contents into a comment and hides it.

    Is there something already like this or is it not possible?


    hi all, attached is an example of a timesheet we use.
    now after lengthy discussions the comany say its not 'in their interest' to use acess so i'm stuck with excel on this one.

    I need to sum a column on many critiera, which i feel maybe a job for the trusty old =SUMPRODUCT. But thing is i need to sum a column based on a date range, Rate, Day.

    I have manually typed in the number i think it should produce but as far as formulas go i'm lost

    1st off need to calcualte date range, which is situated on the top of the spreadsheet.
    2nd some how tell the formula that Normal overtime is either classed as rate 1 Saturdays classed as 1.5 and sundays and bank holidays classed as 2.

    i dont know how to segregate this in a formula, can anyone help!

    Re: Calculating Hours Worked With Overtime Clause

    this works until you put in less than 1hr for lunch
    if someone starts at 9 and finishes at 7 thats 1 hour Overtime, regardless of 30mins lunch or 1hr lunch or No lunch!

    this still brings back 30mins overtime instead of an hour

    Re: Calculating Hours Worked With Overtime Clause

    i've spotted where the formula is falling down.
    if they dont have lunch it works but need to include that regardless if they have 5 mins or 55 mins it will not be included in the overtime, lunch is 1 hour, thats it, none of it should be included in the end result....

    sorry i didnt reply earlier


    Re: Calculating Hours Worked With Overtime Clause

    Well the max you can have for lunch is an hour

    for example, if you started at 8 finished at 5 and 1hr lunch = NO OVERTIME
    if you started at 8 finished at 6 and 1hr lunch = 1hr overtime
    if you started at 8 finished at 6 and 0 lunch = still 1 hour over time

    sorry if this is getting confusing

    Hi all, I need to worked out Hours worked in a timesheet.
    This was the easy part, the hard part is the clause tha HR threw in, which is:

    If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime.
    if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.

    This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it.

    I have basically tried in a formula to replicate this but it works with some data and not with all.

    attached is an example, as you'll see the formula works in some cells, but not others.

    Thanks guys 'n' gals and all fellow gridders!

    Greetings all,

    I've stumbled upon a hurdle whislt developing a current project.

    I need to be able to create a series of files based up data which is needs to be filtered.
    1st of all i need to import data from another spreadsheet, so i am cprrect is assuming i can do this by using the


    method, and copying the data accross into a temp spreadsheet.

    Stage 2 of this i need to extract anything which is displayed "JOB" in column D, and create a new spreadsheet called "JOB_(previousWorkingDay).xls" e.g. JOB_010507.xls

    Stage 3 is the tricky part.
    In column A is a currency and column D a Account.
    I need to create a new file for each Account and Currency.
    so if there is 1200 rows, but only 3 accounts, with 4 currencys in each, the files that output would look like this, and each file would contain that data.

    for example.....




    i think i could do the filtering, but i'm a bit unsure on how to tell it to output the data into the appropriate files.

    Please can anybody help.

    Re: Update Field In Table From Form Combo Box

    There are no relationships, the data is displayed on a single form.
    There are 20 txt boxes in the form and 2 combo box.

    for example:
    5 of the 20 txt boxes are populated from the table TABLE1, leaving 15 blank.
    2 Combo box display Category and Product, you choos a Category in COMBO1 and it List the appropriate Products in COMBO2.

    When the user goes to a record, and inputs all the different values in the 15 txt boxes and chooses the options from the combo box's, i want to be able to populate TABLE1 with the edited data.

    At the moment if i input some data, and select from the list on the combo box and then goto another record and come back, the data isn't saved.

    I've used ADO code to ".AddNew" records, but i dont know how to tell Access to update the records its currently in, and what fields.

    Hope this explains it a little better.

    Hi all,

    I have a form which i bound to a query, in which it gets all the text box's populated from, apart from 2.

    there two combo box's the user needs to populate.

    so on my table, in record 10 all my fields are populated apart from PRODUCT.

    when i open up my form,, and the user scolls to record 10, and chooses a selection from COMBOBOX110, how do i get VBA to update, that field, on that perticular record its looking at, but only populate that field if it changes from last time they scrolled through.

    Re: Rename Files To Folder Name From Root Directory

    OK Thats brilliant, no more shell!


    sorted, i tried this and it worked

    Hi all,
    Its been a while since i've been here, i'm sort of seen that a good thing as i've got to put all the things i've learned here to practical use....


    However i have stumbles on a rather pressing issue. I've been given over 100 folder named like so

    CASH-01-02-2007-21-48 -- CASH-01-02-2007-21-50......and so on

    these are found is a folder called CASH.

    as you can see the folder name is that date and the time it was exported (i gather these came from a 400 system or something)

    In each of these folders is a CSV file called CASH.csv.

    I need VBA to scan all the Sub Directories is C:\CASH\ and rename the files within those folders to the folder name its in.


    Original File: - C:\CASH\CASH-01-02-2007-21-48\CASH.csv
    New file: - C:\CASH\CASH-01-02-2007-21-48\CASH-01-02-2007-21-48.csv

    I have some code which scans the directory and displays the info in the intermediate window,

    but i dont know the rest, can anyone help?