how to i set my code to delete a row once the critera is met
machineName = Worksheets("Log-Import").Cells(logrow, MachCol).Value
If machineName = "LSPR99-0403-01" Then
MsgBox "error find, now get rid of it"
Else
that is what i have for code and right below the msgbox i want it to delete the row that the cell is found in thanks for the help
wow man u are the best but there a still just a few thnks that need to be tweeked one is
If LogCol = "logoff" Then
i get a type mis match with that and how should i call it in the main program, i am calling it like this now
'Determine the next available user row
userrow = UserRowFinder(username)
'Enter the value of this user row to the appropriate user's sheet
Worksheets(username).Cells(userrow, UserDayCol).Value = Worksheets("Log-Import").Cells(logrow, DayCol).Value
Worksheets(username).Cells(userrow, UserDateCol).Value = Worksheets("Log-Import").Cells(logrow, DateCol).Value
Worksheets(username).Cells(userrow, UserLogonCol).Value = Worksheets("Log-Import").Cells(logrow, TimeCol).Value
'Call a logoff function here
Findlogoff (totalitems)
your imput is great thanks for all your help so far
ok i lied i got one more probelm and that is a darn type mismatch
lastrow = totalitems(userstartrow)
i am tring to call the function so i can use it in my for loop but i am gettign the mismatch problem so if you could help that would be great
i keep getting this error when i am tring to call this function
Function totalitems(totalitems)
UserRowCounter = UserStartRow
While Worksheets("Log-Import").Cells(UserRowCounter, UserStartCol) <> ""
UserRowCounter = UserRowCounter + 1
Wend
totalitems = UserRowCounter
End Function
'function to be the do all end all find logoff for each coresponding logon.
Function Findlogoff(totalitems)
itemcount = 0
totalitems (totalitems)
For counter = 1 To totalitems
If LogCol = "logoff" Then
If UsernameCol = username Then
If DayCol = UserDayCol Then
Worksheet(username).Cells(userrow, UserLogoffCol).Value = Worksheets("log-import").Cells(logrow, LogCol).Value
itemcount = itemcount + 1
End If
End If
End If
Next counter
If itemcount = 0 Then
MsgBox "there were no logoffs found for & username & on that day. would you like to continue."
End If
End Function
'Call a logoff function here
Findlogoff (totalitems)
please help this is the last thing i need i promise
While Worksheets("Log-Import").Cells(counter, LogCol).Value And Worksheets("Log-Import").Cells(counter, UsernameCol).Value And Worksheets("Log-Imports").Cells(counter, DayCol).Value <> "Logoff" And username And Cells(counter, DayCol).Value
i cant find the type mismatch please help
ok i have almost got it, i have taking all your advice and now i just need to know how to find the logoff that coresponds to the log on that i pull out, here is my code i have come a long way from this morning all i need is this please help
'1/28/2004 started creating the file
Const LogCol = 1
Const LogStartRow = 1
Const UsernameCol = 2
Const MachCol = 3
Const DayCol = 4
Const DateCol = 5
Const TimeCol = 6
Const MainStartRow = 1
Const UserStartRow = 10
Const UserStartCol = 2
Const UserDayCol = 2
Const UserDateCol = 3
Const UserLogonCol = 4
Const UserLogoffCol = 5
Const UserMachCol = 6
'Function to determine the row for a particular user
Function UserRowFinder(Username)
UserRowCounter = UserStartRow
While Worksheets(Username).Cells(UserRowCounter, UserStartCol) <> ""
UserRowCounter = UserRowCounter + 1
Wend
UserRowFinder = UserRowCounter
End Function
'Function to find the coresponding logoff for the logon
Function LogoffFinder(Logoff)
Logrow = LogStartRow
While Worksheets("Log-Import").Cells(Logrow, LogCol).Value <> ""
'enter code to find the logoff for the logons that have been found below
End Function
'This is the main SORTING Dog used to sort the log file into separate sheets by users
Private Sub btnSort_Click()
Logrow = LogStartRow
While Worksheets("Log-Import").Cells(Logrow, LogCol).Value <> ""
'Determine the username of the current row
Username = Worksheets("Log-Import").Cells(Logrow, UsernameCol).Value
'Determine the next available user row
userrow = UserRowFinder(Username)
'Enter the value of this user row to the appropriate user's sheet
Worksheets(Username).Cells(userrow, UserDayCol).Value = Worksheets("Log-Import").Cells(Logrow, DayCol).Value
Worksheets(Username).Cells(userrow, UserDateCol).Value = Worksheets("Log-Import").Cells(Logrow, DateCol).Value
Worksheets(Username).Cells(userrow, UserLogonCol).Value = Worksheets("Log-Import").Cells(Logrow, TimeCol).Value
'Call a function to find logoff and place appropriately.
Worksheets(Username).Cells(userrow, UserMachCol).Value = Worksheets("Log-Import").Cells(Logrow, MachCol).Value
MsgBox "Username: " & Username & " LogRow: " & Logrow & " Userrow: " & userrow & "DayCol: " & DayCol
Logrow = Logrow + 1
' Wend
Wend
End Sub
that is great can you tell me how to set up the pivot table please
ok this is my problem,
I have a list that i have an inital sort to move them to there pages in excel by the user name. The sort looks like this
Logon cbourassa YORK TUE 9:00 a.m.
Logoff cbourassa YORK TUE 5:00 p.m.
Logon cbourassa YORK WED time
Logon cbourassa PARIS WED time
Logoff cbourassa PARIS WED time
Logoff cbourassa YORK wed time
and i have a list of about 10 entries, now what i want to do is sort threw the list take all the mondays and put them to gether and then all the tuesdays and so on and so forth so it sort of looks like this
Logon Logoff MachineName Hours
Mon 9:00a.m 5:00p.m YORK 8
TUE 8:00a.m 4:00p.m YORK 8
now the only problem i will run into is that sometimes there are multiple logon/logoff for on day becuase i will be on 2 machines, so i need some error checking to make sure that logon and log off match. Then once the days of the week are done i need it to total the hours row and then it will be done. I kno this is alot but if you could help with just a section or all of it, it would be greatly appreicated. THanks
ok i want to sort files to different pages, this is how it is set up
Logon cbourassa YORK tue date/time
Logoff cbourassa YORK tue date/time
Logon cbourassa YORK wed date/time
Logoff cbourassa YORK wed date/time
Logon eleighton BangKOK tue date/time
Logoff eleighton BangKOK tue date/time
Logon eleighton BangKOK wed date/time
Logoff eleighton BangKOK wed date/time
how would i sort them by user name (column b) to there own seprate page
i have fixed all my importing problems my new question is how do i get the sort to pull out all the records that match one thing and put them in a nother page and do that for every file
ok i have gotten the file to import but there is a problem that when it is import from a txt file to the excel sheet it is all in column A, and since it is all in column A i can use the sort to pull out all the bouassa and move them to the next row so everyone is all in there own row, hope that helps if you decide to help me
Ok i am back again and i need help just one more time. This project i need to beable to set a macro to a visual basic buttion in the excel sheet to promt an open file, then with the open file that is selected i want it to be imported to the excel sheet. Once in the sheet i then want to sort the files by each user name. If you have any ideas please post the code so i can be pointed in the right dircetion thanks for all your help in advance, not to say i am on a time frame but i got till 5: p.m est to get this done. So please help me if you can:(
i am really happy with the help i am getting so i just want to say thanks i now have it in a marco but i dont get how to have it run and stuff, i kno i seem kind of stupid i do have my expert excel certification just have not done marcos in ages
now how will it save when i close the program is when i am loged off so i am just a little confused there
wait dont go away waht do you mean date/time for a cell. Whould i have it like
Private Sub Workbook_Open()
then
a1=date/time
end sub
Private Sub Workbook_Close()
a2=date/time
end sub
i hope you are still there to answer me
i am tring to creat a macro that when i log in it will track the time that i am loged in and once i log out it will display the time i was loged into the excel feild. I am hoping to use this as an effective punch clock for my work please help if you can or just point me in the write dircetion. Thanks for your help
