Excel Spreadsheets? - Fantasy Football Cafe 2014 Fantasy Football Cafe


Return to Football Talk

Excel Spreadsheets?

Moderator: Football Moderators

Postby J2thez929 » Mon Aug 15, 2005 4:51 pm

Excellent find ;-D
J2thez929
Hall of Fame Hero
Hall of Fame Hero

User avatar
Fantasy ExpertCafe RankerMock(ing) DrafterEagle EyePick 3 Weekly WinnerSweet 16 SurvivorCafe Blackjack Weekly WinnerLucky Ladders Weekly Winner
Posts: 8041
Joined: 6 Nov 2003
Home Cafe: Football
Location: C-Town, OH - Dawg Bone Country

Postby Netsfan5 » Tue Jun 13, 2006 3:36 pm

Weird, I get this when I open web query...

Image
Image
Netsfan5
Defensive Assistant
Defensive Assistant

User avatar

Posts: 563
Joined: 12 Nov 2005
Home Cafe: Basketball

Postby Amazinz » Tue Jun 13, 2006 4:52 pm

Can you post a larger version of that picture? It's really tough to see what is going on there. Also, if you can post the version number of Excel that you are using.
Image
Amazinz
General Manager
General Manager

User avatar
Mock(ing) DrafterEagle EyePick 3 Weekly Winner
Posts: 2552
Joined: 16 Mar 2004
Home Cafe: Baseball

Postby Sixxgunn » Wed Aug 02, 2006 10:12 am

I get a "web query returned no results" message. What did I do wrong?
[url=http://imageshack.us][img]http://img170.imageshack.us/img170/1065/sixgunn7ao0rc.jpg[/img][/url]
Sixxgunn
General Manager
General Manager

User avatar

Posts: 4887
(Past Year: 3)
Joined: 20 Jul 2004
Home Cafe: Football
Location: Who?

Postby aaawall91 » Mon Jan 01, 2007 2:34 am

Sometimes when I import larger numbers, such as pass yards the numbers turn into ???'s, does anyone know how to fix this, other than manually?
aaawall91
Hall of Fame Hero
Hall of Fame Hero

CafeholicFantasy ExpertCafe RankerMock(ing) DrafterEagle EyeCafe MusketeerLucky Ladders Weekly Winner
Posts: 9934
(Past Year: 63)
Joined: 15 Aug 2004
Home Cafe: Football
Location: East Lansing

Postby aaawall91 » Tue Jan 02, 2007 10:29 pm

Nvm, figured it out, my column width was too small, but how can you do this:

Say in excel there are a list on players

Player A
Player B
Player C
Player D
Player E

How Can I make it so it says

Player A
Player D
Player B
Player C
Player E

without doing all the swapping out? Can I somehow just re-position one at a time instead stead of 2?
aaawall91
Hall of Fame Hero
Hall of Fame Hero

CafeholicFantasy ExpertCafe RankerMock(ing) DrafterEagle EyeCafe MusketeerLucky Ladders Weekly Winner
Posts: 9934
(Past Year: 63)
Joined: 15 Aug 2004
Home Cafe: Football
Location: East Lansing

Postby Amazinz » Wed Jan 03, 2007 1:01 am

You need to use a macro: (not my code but it's open source)


Code: Select all
Sub swapRows()
    ' David McRitchie, 2004-01-05, http://www.mvps.org/dmcritchie/swap.htm
    Dim xlong As Long
    If Selection.Areas.Count <> 2 Then
       MsgBox "Must have exactly two areas for swap." & Chr(10) _
         & "You have " & Selection.Areas.Count & " areas."
       Exit Sub
    End If
    If Selection.Areas(1).Columns.Count <> Cells.Columns.Count Or _
       Selection.Areas(2).Columns.Count <> Cells.Columns.Count Then
       MsgBox "Must select entire Rows, insufficient columns"
       Exit Sub
    End If
    Dim areaSwap1 As Range, areaSwap2 As Range, onepast2 As Range
    '--verify that Area 2 rows follow area 1 rows
    '--so that adjacent single column swap will work.
    If Selection.Areas(1)(1).Row > Selection.Areas(2)(1).Row Then
       Range(Selection.Areas(2).Address & "," & Selection.Areas(1).Address).Select
       Selection.Areas(2).Activate
    End If
    Set areaSwap1 = Selection.Areas(1)
    Set areaSwap2 = Selection.Areas(2)
    Set onepast2 = areaSwap2.Offset(areaSwap2.Rows.Count, 0).EntireRow
    areaSwap2.Cut
    areaSwap1.Resize(1).EntireRow.Insert Shift:=xlShiftDown
    areaSwap1.Cut
    onepast2.Resize(1).EntireRow.Insert Shift:=xlShiftDown
    Range(areaSwap1.Address & "," & areaSwap2.Address).Select
    xlong = ActiveSheet.UsedRange.Columns.Count  'correct lastcell
End Sub
Image
Amazinz
General Manager
General Manager

User avatar
Mock(ing) DrafterEagle EyePick 3 Weekly Winner
Posts: 2552
Joined: 16 Mar 2004
Home Cafe: Baseball

Postby aaawall91 » Thu Jan 04, 2007 11:11 am

Thanks, but I tottaly don't know what to do with that :-o , do I copy paste it into something...or...uhh?
aaawall91
Hall of Fame Hero
Hall of Fame Hero

CafeholicFantasy ExpertCafe RankerMock(ing) DrafterEagle EyeCafe MusketeerLucky Ladders Weekly Winner
Posts: 9934
(Past Year: 63)
Joined: 15 Aug 2004
Home Cafe: Football
Location: East Lansing

Postby Amazinz » Thu Jan 04, 2007 12:19 pm

1. Open up your Excel workbook.

2. On the menu go to: Tools -> Macro -> Visual Basic Editor

3. A new window will be displayed. On the left hand side there will be a project panel that lists a code file for each sheet; such as Sheet1, Sheet2, Sheet3, etc. There will be an extra file named ThisWorkbook. This is the one we want (double-click it).

4. Now back on the right side of that area will be a white workspace. This is the code file ThisWorkbook. Copy the code above and paste it into that window. Now click Save. (There is a toolbar button or you can use the menu File -> Save [Book1]).

5. Close the Visual Basic editor window. You have just created your first macro manually. Now you can highlight the two rows you want to swap. On the menu select Tools -> Macro -> Macros... You will see the macro "ThisWorkbook.swapRows". (This is the name of the function you added through the VB editor). Select swapRows and click the Run button and that's it.

6. (Optional) On the menu select Tools -> Macro -> Macros... and select the swapRows macro but don't press the Run button. Instead click the Options button. A dialog will pop up and you will see a label that reads Shortcut key: CTRL+ and then a blank space. In that space you can type a key, like 'Z', and then from now on you can swap rows by hitting CTRL+Z rather than going through all of the menu steps.
Image
Amazinz
General Manager
General Manager

User avatar
Mock(ing) DrafterEagle EyePick 3 Weekly Winner
Posts: 2552
Joined: 16 Mar 2004
Home Cafe: Baseball

Postby aaawall91 » Thu Jan 04, 2007 12:32 pm

Ok, I should of mentioned I'm running '07 (trial) version of excell, I tried to interprete what you said for whatever version you were speaking of into what version I was running, and thought I was doing it well but it wouldn't let me paste the code into the area so I guess I miss-interpreted. Thanks for your help though.


EDIT: Ok I figured it out and did everything, but there were a couple things I wasn't sure about, for instance saving the Macro, I had to save it as a whole file, so like the file type was a 'Excel Macro-Enabled Workbook' which I think is fine but not sure. And I made the short-Cut key Z, however when I go to use it, it gives me the error "You must have exactly two areas to swap. You have 1 areas." I'm not sure what I did wrong.
aaawall91
Hall of Fame Hero
Hall of Fame Hero

CafeholicFantasy ExpertCafe RankerMock(ing) DrafterEagle EyeCafe MusketeerLucky Ladders Weekly Winner
Posts: 9934
(Past Year: 63)
Joined: 15 Aug 2004
Home Cafe: Football
Location: East Lansing

PreviousNext

Return to Football Talk

Who is online

Users browsing this forum: No registered users and 6 guests

Forums Articles & Tips Start & Sit Sleepers Rankings Leagues


Get Ready...
The 2014 NFL season kicks off in 13:45 hours
(and 54 days)
2014 NFL Schedule


  • Fantasy Football
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact