Solved: Excel Macro Help needed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

spiff72

Thread Starter
Joined
Sep 17, 2008
Messages
5
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-applications/559825-solved-excel-search-macro-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

Thread Starter
Joined
Sep 17, 2008
Messages
5
Here it is.

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

Attachments

spiff72

Thread Starter
Joined
Sep 17, 2008
Messages
5
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
 

Attachments

spiff72

Thread Starter
Joined
Sep 17, 2008
Messages
5
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
 

spiff72

Thread Starter
Joined
Sep 17, 2008
Messages
5
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top