Macro to add total hours

[prdponce]

Thanks in advance for any help you can provided. I have a spreadsheet attached consisting of over 19000 rows. There is blank row after each workweek have ended. the total daily hours are in Column I. I am looking for a macro that will either look at Column E "wed" or column I see total daily hours above the blank row, Add those totals and display the results on the blank row in Column H.

Thanks in advance.

if it not much asking, I added the blank row. before that there were none. is possible, if there a macro that will just look for wed in column E. and then automatically insert the row and does the same operation as above.

Thanks

[xlbiznes]

this is my solution, hope this helps.

[prdponce]

xlbiznes,

Thanks fantastic job. Just what I wanted.

[xlbiznes]

you are welcome,

Please mark this thread as solved if you are happy with the solution provided.

[prdponce]

xlbiznes,

Thanks, I kind of spoke too soon. Some of the people did not work on a Wed, therefore, Tue, Mon or any other day prior to that will be the last entry. Because of it the macro would not work as isnot seeing Wed to perform the function. could this be revise to look at D and is Wed is not there to perform the function on the next prior day?

Thanks

[xlbiznes]

can you post sample raw data, so that i can understand what the scenario is and code accordingly.

[prdponce]

here it is. Thanks

[xlbiznes]

Shall i also delete those rows in the middle that have the column heading ?

[prdponce]

Hey Guys. This code was written by Xlbiznes, it worked for the problem i had at the time File name excelhep. I tried to apply it to file book2 by changing range "i" to "z" and "j" to "aa" but it does not work the macro run without error but do not anything. the macro is suppose to look at D find Friday insert a row and add total hours in column AA.

Any help will be greatly appreciated.



Sub add_subtotal()
'code by xlbiznes - 10th jan 2014
x = 2 ' since there is a column header in row 1, we start from row 2.
tot_hrs = 0 ' to calculate the hours worked
'start loop
Do
If IsNumeric(Range("i" & x)) = True Then 'check if hrs worked is numeric and add to tot_hrs
tot_hrs = tot_hrs + Range("i" & x)
End If

If UCase(Range("d" & x)) = "Friday" Then 'check if end of week has occured
Rows(x + 1).Insert 'insert row
x = x + 1
Range("J" & x) = tot_hrs 'put total hrs worked for the week
tot_hrs = 0 'initalize tot_hrs to 0
End If

x = x + 1 'increment row
If Range("a" & x) = "" Then ' check if you have hit the end of data, if so exit loop
Exit Do
End If
Loop

MsgBox "Process Completed", vbInformation, "Xlbiznes"

End Sub

[prdponce]

Hey guys any comments on how I can make this work. I have a huge file I need to separate. another thing is that "Friday" is repeated on successive lines, Could this be why the code does not work. Thanks

[xlbiznes]

Hi prdponce,

the issue is in this line :

Please Login or Register  to view this content.

change this to

Please Login or Register  to view this content.

The UCase command will convert the string inside column d to upper case, so its expecting the string to be FRIDAY ( = ) and not Friday.

[prdponce]

Thanks. In the latest spreadsheet the Friday is repeats sometimes if there a way that the code skip the fisrt Friday when there are two succesive one and perform the instruction after it.

[xlbiznes]

This should do the job:

Please Login or Register  to view this content.