Posts by Barryj

Re: Formula For Reading Both Text & Numbers

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

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
ADO

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.

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.

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.

Re: 24 hour time

Tried your formula and it comes up with a 0

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.

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.

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]

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?

This is what I had

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

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")
  RngCopy.Copy RngPaste
  Range("D9:D67").Select
    Selection.Font.ColorIndex = 2
    Range("I9:I67").Select
    Selection.Font.ColorIndex = 2
End Sub

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. :?

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:

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.

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? :?

Re: Trigger Macro On Another Sheet

Am getting a error

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:

Re: Trigger Macro On Another Sheet

Thanks for the help :thanx:

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.

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.

Private Sub Worksheet_Activate()
 
 With Sheets("A Grade Rd1").Select
 Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending


Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _




    
    Sheets("A Grade Nett Rd").Select
    Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending


Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _




Sheets("B Grade Rd1").Select
   Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending


Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _


    Sheets("B Grade Nett Rd1").Select
   Range("A2", Range("F31").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending


Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _


End With
End Sub

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.

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.

Private Sub Worksheet_Activate()
 Dim nodupes As New Collection
'clear out any existing data
 With Sheets("A Grade Stroke Final Results")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Rows("2:" & lastrow).ClearContents
 End With
 
'build unique list of players
 shtarr = Array("A Grade Rd1", "A Grade Rd2", "A Grade Rd3")
'cycle through each of the input sheets
 For i = LBound(shtarr) To UBound(shtarr)
  shtlstrow = Sheets(shtarr(i)).Cells(Rows.Count, "A").End(xlUp).Row 'last row in current reference sheet
'build a collection of names
  For Each ce In Sheets(shtarr(i)).Range("A2:A" & shtlstrow)
   On Error Resume Next
   nodupes.Add Item:=ce, key:=CStr(ce)
   On Error GoTo 0
  Next ce
 Next i
 
'output the unique names
 For i = 1 To nodupes.Count
  Sheets("A Grade Stroke Final results").Range("A1").Offset(i).Value = nodupes(i)
 Next i
 
'enter the extraction formulas.  Uses a vlookup to get the data into the relevant column,
'converts to values, and removes any not found results.




 Sheets("A Grade Stroke Final results").Range("B2").Formula = "=vlookup($A2,'a grade rd1'!$A$2:$F$" & Sheets("a grade rd1").Cells(Rows.Count, "A").End(xlUp).Row & ",column(),0)"
 Sheets("A Grade Stroke Final results").Range("B2").AutoFill Destination:=Sheets("A Grade Stroke Final results").Range("B2:F2")
 
 Sheets("A Grade Stroke Final results").Range("G2").Formula = "=vlookup($A2,'a grade rd2'!$A$2:$F$" & Sheets("a grade rd2").Cells(Rows.Count, "A").End(xlUp).Row & ",column()-5,0)"
 Sheets("A Grade Stroke Final results").Range("G2").AutoFill Destination:=Sheets("A Grade Stroke Final results").Range("G2:K2")
 
 Sheets("A Grade Stroke Final results").Range("L2").Formula = "=vlookup($A2,'a grade rd3'!$A$2:$F$" & Sheets("a grade rd3").Cells(Rows.Count, "A").End(xlUp).Row & ",column()-10,0)"
 Sheets("A Grade Stroke Final results").Range("L2").AutoFill Destination:=Sheets("A Grade Stroke Final results").Range("L2:P2")


 
 With Sheets("A Grade Stroke Final Results")
' *********** old formula code *********
  '.Range("Q2:U2").Formula = "=SUMPRODUCT(SMALL(IF(N(OFFSET(B2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(B2:$P2 ,0,{0,5,10},1,1))),{1,2}))"
' *********** old formula code *********
  .Range("Q2:U2").Formula = "=SUM(OFFSET(B2,0,$V2),OFFSET(B2,0,$W2))"
  .Range("V2").Formula = "=getpos(B2,G2,L2,0)"
  .Range("W2").Formula = "=getpos(B2,G2,L2,1)"
  .Range("B2:W2").AutoFill .Range("B2:W2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row - 1)
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("B2:P" & lastrow).Value = .Range("B2:P" & lastrow).Value 'convert to values
  .Range("B2:P" & lastrow).Replace what:="#N/A", replacement:="" 'remove any error results
  
  .Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
   Key1:=.Range("U2"), _
   Order1:=xlAscending, _
   Key2:=.Range("T2"), _
   Order2:=xlAscending, _
   Key3:=.Range("S2"), _
   Order3:=xlAscending
  .Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
   Key1:=.Range("Q2"), _
   Order1:=xlAscending, _
   Key2:=.Range("R2"), _
   Order2:=xlAscending


 End With
 
End Sub

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

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.

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.

Thanks inadvance for any help you guys can provide. :thanx:

Private Sub WorkSheet_Calculate()
    Dim rng As Range
        '   Set the range as Dynamic
        Set rng = Range([B19], [V65536].End(xlUp))
Dim r As Range, grade, c As Range
Dim i As Integer, sn, x As Range
grade = Array("A")
sn = Array("Score sheet Stableford")
Application.ScreenUpdating = False
For i = LBound(sn) To UBound(sn)
Sheets(sn(i)).Cells.Resize(Cells.Rows.Count - 1).Offset(1).ClearContents
Next
With Sheets("stableford")
For Each r In .Range("a19", .Range("a65536").End(xlUp))
If r.Offset(0, 1).Value = "" Then GoTo SkipIt1
For i = LBound(grade) To UBound(grade)
If r.Value = grade(i) Then
Set x = Sheets(sn(i)).Range("a65536").End(xlUp).Offset(1)
x.Value = r.Offset(, 1).Value
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 22).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 23).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
Exit For
End If
SkipIt1:
Next
Next
End With
Application.ScreenUpdating = True


End Sub

Macro on second sheet

Private Sub worksheet_Activate()
Range("A2", Range("F65536").End(xlUp).Address).Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending _
, Key2:=Range("E2"), Order2:=xlAscending _
, Key3:=Range("D2"), Order3:=xlAscending


Selection.Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending _


End Sub