Posts by weebilko

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Yep, yep that's it, sorry it was all about my poor communications. Very grateful.

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Yes that's it.

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

I’m sorry not quite there yet; say AT2 is set at 7 (2 nos) and you input 3, 4, 5, 5 into the grid this happens 3, 4 (red), 5 (red), 5 but it should be 3, 4, 5 (red), 5 (red) and if TT2 is set at 5 (1 no) if you insert say 4,4 then it seems to work in that 1 no turns red but there is a Run-time error at L3?

I don’t know if I can explain it more fully or should I send you a ‘sample’ worksheet?

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Sorry you were up so late don't stay up late tonight. We are nearly there the 2 Red numbers work fine but when it's for a singe Red if you have say 2 duplicated numbers (say the 2 highest numbers are 22) then both turn Red instead of a single turning Red. Regards

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Sorry to be slow in coming back to you as at last got into VB Editor. Nearly there works fine but above AT6 if there are say 3 equal 1st nos or 2 equal 2nd nos it returns them all in Red (more than 2) if you follow and the same if it's meant to be 1 Red no.

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Yes, but if I then put in another higher no that one doesn't turn Red but the 2 lower ones stay Red.

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Thanks but sorry I just deleted your nos in Grid 1 and re-entered 4 of my own at random and everyone turned Red?

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

I'm sorry we seem to be at cross purposes as if there are say 2 equal high nos these 2 should be returned but not the next highest. There should only ever be 2 nos in total in red if over 5 in AT2 and only ever 1 if below. PS I cant seem to find the macros? and all grids should have 5 rows.

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Thanks for all your help and that is a neat ‘looping ranges’ file fess. I attach your sample workbook KjBox with my intended layout but can’t seem to get it work, no doubt it’s me. Can you have a look please. Regards

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Many thanks fess works great, just a couple of things that you could maybe help me with:

1. You are correct the macro should be triggered when it is >5 (single red when 5 or under 2 reds when 6 or over) so can you amend please.

2. There will be 9 similar but separate grids (1 that your macro will cover + 8 others) and wonder if I can just use your macro and set up the 8 others by renaming the macro and changing the ‘range’ to suit.

Very grateful when you can find the time to amend. Regards

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Many thanks fess, yes you are correct re the >5 sometimes you don't see the 'bleeding obvious'. OK will have a play around and get back to you, once again many thanks. Regards

Re: Macro to find/return 1 or 2 highest values in Red in a spreadsheet grid

Thanks for looking will need to create a sample as original is too large and post it tomorrow Regards

Edited(New info) I also realise that I have sent the wrong macro .docx so let me rethink the project and send an fie copy.

I have a grid H4:Y8 in a spreadsheet in which various values are entered, some may be duplicated and I have a VBA macro (.docx attached) which will find only the 2 highest numbers and returns them in red and even if there are duplicate numbers it will still only find a maximum of 2 and it works fine.

I now wish to expand it so that if I enter a value between and including 4 to 10 into cell AT2 and if it is less than 6 then a macro will find only the single highest number (even if the numbers are duplicated) in the grid and the number will be changed from black to red but if the number entered into AT2 is more than 5 then only the 2 highest numbers, again even if there are duplicates, are changed to red, as now.

I would be very grateful if anyone can help increase the scope of the macro and I hope I have explained it sufficiently without attaching the actual spreadsheet file which is large.

Regards

Re: Reduce fist name to an initial + surname or surname only

Excellent that's it. Many, many thanks.

Re: Reduce fist name to an initial + surname or surname only

Yes, you are absolutely correct in the complications that arise as it returns a Scottish name such as McGlynn as Mcglynn, so I think I will take the "least worst version", so many thanks.

Re: Reduce fist name to an initial + surname or surname only

Sorry for getting my original post a bit "scrambled" don't know what happened. Many thanks guys I should have said it's not just lists that need to be copied over there are a number of individual cells but I do understand exactly what you mean.

The best I can come up with is:

LEFT($A1)&" "&TRIM(RIGHT(SUBSTITUTE($A1," ",REPT(" ",99)),99))

and it works pretty well apart from, I think, only one problem in that it returns T TBC instead of TBC so I will live with that. Regards.

Re: Reduce fist name to an initial + surname or surname only

Thank you very much for your interest and speedy reply but wonder if there is a formula I could use rather than VB code?

I have a sports spreadsheet with names in col A. Is there a way that I can copy the name to col B but reduce the first name to an initial plus the surname to col B.

To complicate things some of the names have a surname only with no initial, some have an initial only not a full first name and some are in caps only, samples as follows:

A B
John McGlynn J McGlynn
Peter Collins P Collins
TBC TBC
A Evans A Evans

Re: Extract 1st name as an initial + surname

Exactly what I was after but never though it would be as complicated. Many thanks for such a prompt reply.

In cell B1 I have a name John Smith and want to reduce the 1st name to an initial and copy it into B2 as J Smith.

I'm sure it's simple but my mind has gone blank, it's old age. I have tried LEFT(B1)&" "&B1 but it doesn't work.

Any help would be greatly appreciated.