Replies

Hi Randy,

thanks very much for you advice, I will try this method today, and get back to you with the result..

kind regards

Steve – Southampton UK

Hi Steve

hope you have a nice vacation, thanks very much for all your help, I will mess around with this when I get to work in the morning.

kind regards

Steve – Southampton UK

Hi again Steve,
I was wondering how to insert this code?

I have opened the vba editor [alt=f11], and inserted your code, when I try to run it, it asks for a macro name, what name do I give it? Unfortunately I am not an expert on the coding side of things :mellow:

Any further help please Steve, then I can find out whether it works

kind regards

Steve – Southampton UK

Hi again gentlemen,

okay, the exact cells in question are ALWAYS, B9 (this is where the YES is) and the deletion OR blanking is the 3 cells above it, however this is required to work on the right, literally as far right as I require it to go. See attached basic illustration.

29273-Spreadsheet

Hi Steve, thanks very much indeed for the rapid response, I have inserted this picture so you can see what I am trying to do.

29263-Spreadsheet

I really do not now which one to use, how do I insert this macro? Perhaps there may be an easier way?

Once again, thanks very much for having a look at this for me.

Steve – Southampton UK

I’m with Steve. I tried to create a UDF to solve the problem but Ranges in VBA are also not 3D compatible, e.g.
For each oCell in rng…:cheers:

Thanks very much indeed for your help..

regards

Steve

You could create an intermediate list in a sheeto of the D4 values from each of the sheets and then do a lookup on this list to find the maximum and the sheet associated with it…

Steve

Thanks for the reply Steve.

I have rewritten this in another way, I think I might be confusing the issue ;(

I have this formula in a cell =MAX(ABJ:Williams!D1) giving the largest figure in cell D1 across multiple sheets, this works fine, however I would like to also know the name of the worksheet it got the answer from?

Hope you can still help.

Regards Steve

Hi chaps..

thanks very much indeed for all responses, I will try them out this evening, its currently 0840hrs in the UK. I will report back later..

ta Steve – Southampton UK

Hi again HeyJude,

I took your advice and checked out: http://www.office-outlook.com/outloo….php/m/243147/ and sorted this one from the registry, using the details from – kta_office

Once again thanks very much for you help

ta Steve – Southampton UK

Hi again HeyJude…

ta for the advice, when I get back to the office in the morning I will try it, and get back to you with the results..

thanks once again

Steve – Southampton UK

Hi HeyJude

yes I do have it installed.

Do you think this is the problem?

Steve

If Microsoft Security Essentials is being offered as updates, this concerns me! I run Norton Internet Security 2011 in my Windows 7 laptop and Symantec Endpoint Protection on my XP Professional desktop. Both are exquisite and very complete security suites! I am concerned that these Security Essentials updates will begin to interfere with the proper operation of these security suites. It has been my experience that these Microsoft updates have a nasty habit of messing with other peoples stuff, especially if it is better than theirs! Sorry but I pay for my security, I don’t trust anything that’s free. There is just to much stuff that the free stuff misses. I don’t know how many times I’ve had to reinstall an entire computer because they were running free software which just couldn’t cut it!I do not want one of these updates to overwrite the firewall I’ve paid good money for and unfortunately every security item I’ve gotten by accident from Microsoft has mucked with my installed security suite and I’m not about to go through all that again! I’m hoping that the Security Essentials is an optional update so I can chose not to download it! I’d rather stick with what I have, it’s clearly way better!

Good afternoon to you all,

I feel compelled to reply regarding comments made by a couple of your fine readers. I have installed MSE on at least 20 different PC’s over the last year or so. And I have to say that I have not been confronted with one single issue, however I will say that I haven’t installed it on any Laptops with only 512mb ram as I feel that by today standards your asking a lot of any coder to create security type software in today marketplace which sits on top of the OS with such a small amount of ram. I would also like to comment on “I don’t trust anything that’s free” comment. Anybody who has been in computing as long as some of the readers on Woody’s knows only too well that there is some fantastic FREE software out there! Irfanview – Systemexplorer – OpenOffice – Gimp – CCleaner – Handbrake to name just a few and these are just som of the Windows ones, there are countless others for Linux, Mac etc. Imagine the hundreds of hours these people have tirelessly spent in front of a screen making this fine software, for persons with a poor fund of knowledge to engage in negative comments. Remember not everybody has the funds to pay for the likes of ‘Symantec’ etc. who imho charge ridiculous amounts of money for their software, and then you have to update it AND pay for this service. This one is a shout out for the little people who put the hours in for the sake of philanphropy. And as for MSE, it’s free at the moment so don’t surrender your hard earned cash when this does the job just as well.

Hopefully I haven’t offended anybody on this one, if I have I apologise…

Kind regards

Steve
Woodys Office Watch is the best….. :rolleyes:

Here is a method that is “almost” what you desire.

In a module add the following function:
[codebox]
Option Explicit
Function TimeSum(str As String)
Dim AWF As WorksheetFunction
Set AWF = Application.WorksheetFunction
TimeSum = Evaluate(“=timevalue(” & Chr(34) & _
AWF.Substitute(str, “+”, ” AM” & Chr(34) & _
“)+timevalue(” & Chr(34)) & ” AM” & Chr(34) & “)”)
Set AWF = Nothing
End Function
[/codebox]

Then in a cell you can use it to sum the times. Use it like:
=Timesum(“1:30+2+0:45”)

This is close to what you wanted only in addition to entering all the values you must preface the function name and enter the list of times as a string. If desired the function name can be shortened (change it both places in the module…)

Steve

Thanks very much for your help, I will endeavour to try your method today, when I get a spare moment….Steve

Well first and foremost,

thanks very much to all of you kind people for helping me out with this problem, your all very kind

I would just like to add that wouldn’t you guys think that a piece of software that is soooo sophisticated would indeed be able to do what I have requested it to do in the manner in which I asked it…. and to all the doubters out there who will come back with this can NEVER be done “remember NOTHING is impossible”…

thanks to you all once again….Hare Krysna!!

I am not sure what you want: If you type =1+30/60+2+23/60 into the formual bar, you will get the answer in the selected cell. If you type (for example) 1 into cell A1, =30/60 into cell A2, 2 into cell A3 and =23/60 into cell A4 and then type =sum(A1:A4) into cell A5 you will get the same answer. Either way, excel will calculate the total for you. To enter a whole hour and a number of minutes, just type in minutes/60 and add to the whole number hour wherever applicable, either in the formula bar; or in adjacent cells and then sum all cells.

Alternatively: (Neater) Format your cells for the entry of degrees, minutes, seconds (slightly modified) – see below. You then enter your data in the format hh:mm:ss (hours:minutes:seconds). Adding (summing) such data will yield a correct answer (as opposed to formatting the cells in the “time” format which would always adjust the answer to fit in to the 24-hour clock). To do this, select: format, cells, time, custom. In the space beneath “type”, enter the following: [hh] mm’ ss”

Your cells will now accept data entered as follows: 04:32 (4 hours 32 minutes). See attached example

Thanks very much Peter for taking the timeout to look at my query, and thanks for the download, as I mentioned earlier I am familiar with adding them up in columns, the difficulty I face is trying to add up the hours and minutes in a single cell. Please have a look at my latest posting for more details…kind regards..Steve