[spiff72]

Hi everyone - first time poster here...

I am looking for a way to do the following for a Fantasy Hockey draft:

I have a spreadsheet of all skaters (including Centers, Left Wings, Right Wings, and Defensemen), that includes columns for rank (among players in the same position), overall rank (among all players), position, name, team, and other miscellaneous stats. I found a macro in the following link in this forum that provides a nice search algorithm:

http://forums.techguy.org/business-a...ro-needed.html

I was able to modify this to suit my need to search the entire list of players (by name), and it returns all players where part of the name matches. I want to use this info to find their name in the list (manually, based on their ranking, which is how the main list is sorted).

Thus, once a player is drafted, I search for their name using my modified macro, and then locate their name in the main list. I then highlight the row of info, and apply another macro to it that changes the text color, and applies changes the text to "strikethrough".

The challenging part is that I want to create another worksheet that contains a lists of the top 5 or 10 Centers, top 5-10 Defensemen, etc. The macro could be run with a button press to refresh the lists. The idea is that it would populate these sub-lists with the names and stats of the top ranked (highest in the list) players for each position, but it would ignore rows that have had the strikethrough property applied to the text.

I can post an example spreadsheet with a few of the players listed later this evening if it helps.

I am also open to suggestions of a better way to mark players as drafted...

Thanks in advance!
Jeff

[spiff72]

Here it is.

The All Skaters tab contains the main list, and the Draft Page tab contains the initial skeleton of top players.

[spiff72]

Hello again...

Here is a modified version of the spreadsheet, with everything happening on one sheet.

I wrote another macro (called SearchDef) based on the search macro that does what I want (finding positional players that aren't already selected - i.e. strikethrough = false), but I can only figure out how to do it on one sheet. I also figured out how to limit it to a specific number of players (5 in this case).

I want to be able to copy the info the the sheet called Draft_Page. I just can't figure out how to make this work. Can anyone help?

Thanks in advance!
Jeff

[computerman29642]

what about using a Pivot Table to display the top ten?

[spiff72]

Computeman,

Actually, I have made some progress.

I have everything working (I decided I didn't care if everything was on one sheet), but I have one more dilemma.

I want one more macro that will take as an argument the currently highlighted cell (a players name from my search results list), and then search for it in my main player list, select the row, and apply the strikethrough and color attributes to the text.

It chokes on the UCase(Range(ActiveCell)) - this must be an invalid argument...I am also not sure about the Cell.Offset line (it hasn't gotten that far in the macro yet, so I don't know if that is valid code either.

Here is what I have tried:
Sub mark_as_drafted()
FindWhat = UCase(Range(ActiveCell))
For Each Cell In Range("AJ3", Range("AJ" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -2).Resize(, 12).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 46
End With
End If
Next Cell
End Sub


Thanks,
Jeff

[computerman29642]

Can you attach an updated file?

[spiff72]

Computerman,

Actually, I am marking this as solved...

I found my error. I needed to add the .Value after ActiveCell, and then it worked fine.

Thanks!

Sub mark_as_drafted()
FindWhat = UCase(Range(ActiveCell.Value))
For Each Cell In Range("AJ3", Range("AJ" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -2).Resize(, 12).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 46
End With
End If
Next Cell
End Sub

[computerman29642]

I'm glad you were able to get your issue resolved. Just let us know if you need any further help.