DWolf75
02-21-2011, 07:28 PM
Hi, I'm new to VBA and was working on the following code for my golf league to find skins (the lowest score on a hole). If I run the code without the text in red it works, but will not look to see if there is only one score with that lowest value. However when I add the red text to the code, it gets hung up. It will post the lowest score for the first hole, but then get hung up. If anyone has any suggestions, I'd greatly appreciate it.
Sub FindSkins()
Dim MyCell As Range
Dim MyCount As Long
Dim NR As Long
Dim Skins As Double
wsc = 3
NR = 21
Do Until wsc = 12
Set MyRange = Range(Cells(7, wsc), Cells(34, wsc))
Skins = Application.WorksheetFunction.Min(MyRange)
MyCount = Application.WorksheetFunction.CountIf(MyRange, Skins)
If MyCount = 1 Then
Set MyCell = Range(Cells(7, wsc), Cells(34, wsc)).Find(Skins, LookIn:=xlValues, Lookat:=xlWhole)
Worksheets("Sheet1").Cells(NR, 78) = Skins
Worksheets("Sheet1").Cells(NR, 32) = ActiveSheet.Cells(5, wsc)
Worksheets("Sheet1").Cells(NR, 33) = ActiveSheet.Cells(MyCell.Row, "A")
wsc = wsc + 1
NR = NR + 1
End If
Loop
End Sub
Sub FindSkins()
Dim MyCell As Range
Dim MyCount As Long
Dim NR As Long
Dim Skins As Double
wsc = 3
NR = 21
Do Until wsc = 12
Set MyRange = Range(Cells(7, wsc), Cells(34, wsc))
Skins = Application.WorksheetFunction.Min(MyRange)
MyCount = Application.WorksheetFunction.CountIf(MyRange, Skins)
If MyCount = 1 Then
Set MyCell = Range(Cells(7, wsc), Cells(34, wsc)).Find(Skins, LookIn:=xlValues, Lookat:=xlWhole)
Worksheets("Sheet1").Cells(NR, 78) = Skins
Worksheets("Sheet1").Cells(NR, 32) = ActiveSheet.Cells(5, wsc)
Worksheets("Sheet1").Cells(NR, 33) = ActiveSheet.Cells(MyCell.Row, "A")
wsc = wsc + 1
NR = NR + 1
End If
Loop
End Sub