# Posts by Barryj

• ## Formula: Last x Characters, Text or Numeric

Re: Formula For Reading Both Text &amp; Numbers

Sorry for the delay, but it works great, thanks TheCringe2.

• ## Formula: Last x Characters, Text or Numeric

I am trying to write a formula that will recgonise either text or numicial value as the result is used with a match formula.

In column C I have data as follows:

1400 SBY
1230 9985

I am using a =--RIGHT(C4,4) formula in column E to get the required data and then using my match formula to extract other data.

How can I rewrite the above formula so that it can read either text or numbers that will allow my match formula to work.

• ## Time calculation

Re: Time calculation

Thankyou dave for the formula, it works great, I did do a bit of research before posting for this problem and did not find anything that I could adapt.

Thanks again for all the assistance.

• ## Time calculation

I am trying to add the total time and actual time of some employees each fortnight, some of the cells will only contain a - and others will contain their hours across the 2 week period.

Can anyone help me with a formula that will add up these times.

When I started with just adding the relevent cells, after adding 2 cells it did'nt work, hope this makes some sense.

I have included a attachment that hopfully will help.

Thanks.

• ## Calculate Times That Span 2 Days

Re: 24 hour time

Tried your formula and it comes up with a 0

• ## Calculate Times That Span 2 Days

I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time.

In cell D6 I have starting time of 23:00
In cell E6 I have finish time of 07:00

This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently.

The formula in F6 is E6-D6.

• ## Hide cells with Macro not working properly.

Re: Hide cells with Macro not working properly.

Thankyou Batman, that has solved the problem:cheers: sorry for the muck up with the VBA tags.

• ## Hide cells with Macro not working properly.

Re: Hide cells with Macro not working properly.

I tried this but it still effects the original workbook sheet even though I have specified the for this to happen on the workbook where the data is pasted.

[Code]Sub Copy()
Dim RngCopy As Range
Dim RngPaste As Range

Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:I67")
Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:I67")
RngCopy.Copy RngPaste
With Workbooks("Spare Parts Email.xls").Sheets("Handicapps")
Range("D9:D67").Select
Selection.Font.ColorIndex = 2
Range("I9:I67").Select
Selection.Font.ColorIndex = 2
End With
End Sub[Code]

• ## Hide cells with Macro not working properly.

I have this code which is working fine coping from one workbook to another, but on the sheet where the data is posted I want to hide or delete cells D9:D67 & F9:F67.

I recorded some code to alter my original code but it also hides the data on the sheet where the data is copied from, any ideas how to fix this?

Code
``````Sub Copy()
Dim RngCopy As Range
Dim RngPaste As Range
Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:H67")
Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:H67")
End Sub``````

This is what I changed it to

• ## Formula for matching and moving contents

Re: Formula for matching and moving contents

Your formula works fine in the ranking and showing the best result for each colum, but its not quite what I have in mind, have included another workbook with some formula in how I think it should work.

But because I dont know how to include more than 2 arguments in these formulas this is where I am getting stuck.

Hope this helps explain things a little better. :?

• ## Formula for matching and moving contents

Re: Formula for matching and moving contents

[QUOTE=Joebbshop]I think I understand basically what you want. See attached.

The main difficulty is in not giving a rank in the Nett competition to the winner of the Gross competition and to not leave a hole where their rank would be. To accomplish, I used this formula:

Code
``````From cell F2

=IF(D2=1,"",IF(RANK(E2,E\$2:E\$10)>MATCH(MAX(C\$2:C\$10),C\$2:C\$10,0),RANK(E2,E\$2:E\$10)-1,RANK(E2,E\$2:E\$10)))``````

First part doesn't give a Nett rank to the winner of the Gross.

Rest of formula checks to see what Nett ranking the winner of the Gross competion would have had. If the ranking of the current cell is greater than the ranking of the Gross winner, subtract 1 to close the hole.

Makes sense?

Alex.

• ## Formula for matching and moving contents

I Have a list of names that are a result of a golf competition, they are divided into 2 columns, one their Gross score & their Nett score.

What I want to do is determine the winner of the Nett competition, a person can only claim one trophy so this is where it gets complicated.

In list 1 the Gross winner could also be the Nett winner but as they can only claim 1 trophy they will take the Gross Trophy.

this is what should happen, IF H8=E8,E9,H8 and so on so that if the same name appears in the same position on both lists then the next name is elevated.

Hope that someone can understand this and help? :?

• ## Trigger Macro On Another Sheet

Re: Trigger Macro On Another Sheet

Am getting a error

Code
``Call SortWorksheet_Range``

on the activate sheet. have included a file of the sheets required to be sorted, the results sheet is blank at this point as I have not linked it to the results page yet.

As long as the macro runs and does the sort is the main thing. :thanx:

• ## Trigger Macro On Another Sheet

Re: Trigger Macro On Another Sheet

Thanks for the help :thanx:

• ## Trigger Macro On Another Sheet

Re: Trigger Macro On Another Sheet

Ok, have tried that and it works quite well on 1 sheet but multiple sheets is quite slow, plan B, I recorded this macro that I would have hoped would sort the 4 sheets, but I am getting a 1004 run time error on line 3 of the first part of the Macro.

Code
``Range("A2", Range("F31").End(xlUp).Address).Select``

Can anyone either point me in the right direction or modify the code to run on these 4 sheets.

• ## Activate sheet to run macro in another workbook

Re: Activate sheet to run macro in another workbook

Sorry, The macro is currently in a workbook named golf file, I want to put it in a new workbook called results and when the results workbook is activated the macro will run and search through the workbook golf file and retreive the results as it currently does.

Sorry about the confusion. Thanks for the help.

• ## Activate sheet to run macro in another workbook

I have the Macro below which currently runs in a workbook called Golf File,
I want to have this macro in a seperate workbook as it produces results, and is only required at the very end of the season, how can I modiify it so that when I activate the sheet in the new workbook that it will open and run the macro through the workbook Golf File.

I hope this makes some sense, and thanks for any help :?

• ## Trigger Macro On Another Sheet

Re: Trigger Macro On Another Sheet

I tried to get this thing to work with no luck, have attached a copy of file with macro,s in each sheet, would someone be able to show me where to alter the macro and where to put it in each sheet.

Thanks for any help.

• ## Trigger Macro On Another Sheet

I was hoping someone could show me how I could get the first macro to trigger the second macro, currently the second macro is a sheet activate macro.

I want to get it to run each time the first macro runs so that I can hide the second sheet as it only sorts data that is linked to a third sheet which shows the results.