football form table from results

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Nov 8, 2013

• #2

Please try this macro on a copy of your worksheet,

Code:

Sub fillintheresults()
    Range("L2:XFD50").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For i = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("xfd" & i).End(xlToLeft).Column
            If Cells(j, 2).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 8).Value
            End If
            If Cells(j, 6).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next i
End Sub

 

Upvote 0

P

Peterfc2

Active Member

Joined

Jan 2, 2004

Messages

394

Office Version

1. 2013

Platform

1. Windows

• Nov 8, 2013

• #3

Momentman said:

Please try this macro on a copy of your worksheet,

Code:

Sub fillintheresults()
    Range("L2:XFD50").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For i = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("xfd" & i).End(xlToLeft).Column
            If Cells(j, 2).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 8).Value
            End If
            If Cells(j, 6).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next i
End Sub

Click to expand...

Range("L2:XFD50").ClearContents
METHOD RANGE OF OBJECT GLOBAL FAILED

Get the above error!
Any ideas?

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Nov 8, 2013

• #4

Well, that line only seeks to clear the "result range" of any data prior to running the macro. Lets try this, comment that line out of the macro, manually delete the W,W,L,L and run the code again

 

Upvote 0

P

Peterfc2

Active Member

Joined

Jan 2, 2004

Messages

394

Office Version

1. 2013

Platform

1. Windows

• Nov 8, 2013

• #5

Sorry. Maybe I've not made it clear the results I want. I'm happy with homewld and awaywld. They work fine when I add more results.
So I recap..
In K2 is TOM. The data in L2 M2 N2 I have typed in to show the thing I'm after. o.k.
So toms form this season is..
Row 2 he won (his first game)-Row 4 he won (his second game)-Row 5 he lost ( his third game)
So in L2 WOULD BE W - M2 WOULD BE W - N2 WOULD BE L
If tom draws his fourth game then O2 should equal D.
ETC.

Hope that clarifies the what i would like to do.

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Nov 8, 2013

• #6

I do get what you want, see the results as obtained from my macro after I added 2 more matches to the already existing ones

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Home				Away		HimeRes	AwayRes
2		tom	2		0	sum		W	L		tom	W	W	L	W
3		pat	2		2	sum		D	D		pat	D	L	L
4		tom	3		2	pat		W	L		sum	L	D	D
5		pot	4		0	tom		W	L		pot	W	D
6		tom	3		2	pat		W	L
7		pot	1		1	sum		D	D
Sheet4

 

Upvote 0

P

Peterfc2

Active Member

Joined

Jan 2, 2004

Messages

394

Office Version

1. 2013

Platform

1. Windows

• Nov 8, 2013

• #7

o.k. I'll have a play.
lastcol = Range("xfd" & i).End(xlToLeft).Column
I comment out the clearing bit.
stops at Range"fxd"

You running the macro has first written? I'm excel 2000 ( I know, lol) should it still work.

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Nov 8, 2013

• #8

Wow, excel 2000! really can't say

 

Upvote 0

P

Peterfc2

Active Member

Joined

Jan 2, 2004

Messages

394

Office Version

1. 2013

Platform

1. Windows

• Nov 8, 2013

• #9

Really is what I asked for and works for you. Will have another go later. Thanks

 

Upvote 0

P

Peterfc2

Active Member

Joined

Jan 2, 2004

Messages

394

Office Version

1. 2013

Platform

1. Windows

• Nov 9, 2013

• #10

lastcol = Range("xfd" & i).End(xlToLeft).Column

Right had a play with it and its the "xfd" that the macro stops at. Baffles me. I'm open to suggestions?

 

Upvote 0