Posts by error#9

error#9 · Aug 24th 2007

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?

error#9 · Aug 23rd 2007

Re: Transfer Cells Values To Cell Comments

OMG!!!!!!!!!
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

error#9 · Aug 22nd 2007

Re: Paste Cells As Comments

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

Code

ActiveCell

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

error#9 · Aug 22nd 2007

Re: Paste Cells As Comments

This is bugging on a few things.

On this line

Code

myTargetCell_COMMENT_TEXT = myTargetCell_COMMENT.Comment.Text

it says

Quote

Object or with method not found

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

error#9 · Aug 22nd 2007

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?

Thanks

error#9 · Aug 7th 2007

Re: Formula To Calculate Time Between Dates And Other Criteria

OMG!
Works Perfect!
I use SUMPRODUCT at time but not like that, is it possible to explain how that works!
I wish to understand the formula, not steal it!

Thanks

error#9 · Aug 7th 2007

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!

error#9 · Jul 24th 2007

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

error#9 · Jul 24th 2007

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

thanks

error#9 · Jul 23rd 2007

Re: Calculating Hours Worked With Overtime Clause

yeah, it looks like its working, good skills my man!
i will submit this To them, if they dont like it then they can DO ONE!!!

cheers dude!

error#9 · Jul 23rd 2007

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

error#9 · Jul 23rd 2007

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!

error#9 · May 21st 2007

Re: Outputting Files Based On Filters Vba

I think i will do this is access!!

error#9 · May 21st 2007

Re: Outputting Files Based On Filters Vba

Hi,
I'll attach a spreadsheet with some test data on sure.

Attached is a spreadsheet with 20 clients, and different currency info.
So in essence if there are 20 clients with 4 different currencies, there would be 8- files, i've also attached a spreadsheet will all currencies on it (just for info)

error#9 · May 17th 2007

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

Code

Applications.GetOpenFilename

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.....

340985_GBP_01052007.xls
340985_AUD_01052007.xls
340985_USD_01052007.xls
340985_NOK_01052007.xls

445554_GBP_01052007.xls
445554_AUD_01052007.xls
445554_USD_01052007.xls
445554_NOK_01052007.xls

675567_GBP_01052007.xls
675567_AUD_01052007.xls
675567_USD_01052007.xls
675567_NOK_01052007.xls

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.

error#9 · Apr 10th 2007

Re: Update Field In Table From Form Combo Box

Wow, ok, no adodb, less messy and i like it!
I gather as my drop down boxes are unbound i'll just need to requery them when i skip to the next record?

error#9 · Apr 3rd 2007

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.

error#9 · Mar 29th 2007

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.

error#9 · Mar 16th 2007

Re: Rename Files To Folder Name From Root Directory

OK Thats brilliant, no more shell!

hahaha

sorted, i tried this and it worked

Code

Sub Rename_File_As_Subfolder_Name()


Dim FS As FileSearch
Dim FF As Long
Dim FN As String
Dim LBS As Integer
Dim PBS As Integer
Dim LF As String


   Set FS = Application.FileSearch




   With FS


      .NewSearch


      .Filename = "cash.csv"


      .FileType = msoFileTypeAllFiles


      .LookIn = "Z:\Projects\xxxxxxxx\CASH\"


      .SearchSubFolders = True


      .Execute msoSortByFileName, msoSortOrderAscending, True


   End With


   For FF = 1 To FS.FoundFiles.Count


      FN = FS.FoundFiles(FF)


      LBS = InStrRev(FN, "\")


      PBS = InStrRev(Left(FN, LBS - 1), "\")


      LF = Mid(FN, PBS + 1, LBS - PBS - 1)


      Name FN As Left(FN, LBS - 1) & "\" & LF & Right(FN, 4)


   Next FF
  


   Set FS = Nothing


   


End Sub

Display More

error#9 · Mar 16th 2007

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.

Example:

Original File: - C:\CASH\CASH-01-02-2007-21-48\CASH.csv
to
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,

Code

Sub Start()




    Dim FSO As Scripting.FileSystemObject
    Dim TopFolder As Scripting.Folder




    Set FSO = New Scripting.FileSystemObject
    Set TopFolder = FSO.GetFolder("Z:\Projects\Matthew Truslove\CASH") '<< CHANGE
    ProcessOneFolder FSO, TopFolder




End Sub




Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, F As Scripting.Folder)




    Dim OneFolder As Scripting.Folder
    Dim OneFile As Scripting.File




    For Each OneFolder In F.SubFolders
        ProcessOneFolder FSO, OneFolder
    Next OneFolder
    For Each OneFile In F.Files
        Debug.Print OneFile.Name, OneFile.ParentFolder.Path, OneFile.Path
    Next OneFile




End Sub

Display More

but i dont know the rest, can anyone help?

Thanks