find cell value

[gr_zaki]

i try to use HtmlMaker2.32 but always get an error in vb but i include the sample xls file for anyone who can help me ...
in the xls file i include my problem and explanation
Thanks a lot for your time
http://www.geocities.com/gr_zaki/HELP.xls

 

[Andrew Poulsom]

$B$2:$B$17&$G$2:$G$17 concatenates each entry in column B (Home teams) with column G (running count of matches played). So the first item in the array would be Panahaiki1, the second Aigaleo1 and so on.

SUBSTITUTE(J$1,"MATCH_","") changes MATCH_1 to 1. This is concatenated with the contents of cell I2 to produce (also) Panahaiki1.

So ($B$2:$B$17&$G$2:$G$17=$I2&SUBSTITUTE(J$1,"MATCH_","")) compares each item in the array $B$2:$B$17&$G$2:$G$17 with Panahaiki1 and returns True if found, False if not. These Boolean values (True = 1, False = 0) are multiplied by each item in the array $F$2:$F$17 (the ID) to return an array containing the number or zero. The resulting array is added up, returning the matching ID because all the other values are zero.

Hope that helps.

 

[gr_zaki]

all went ok friend thank you... i noticed that my main problem was that your version of excel uses , instead of ; to express "then" that was the problem my excel couldn't understand.
may i ask i final question
if a make a table just like you showed me for the all the season for 24 matches in the home table... if a team has 4 matches and another one has 3, the one with the three matches will take 0 in the cell. this is not fair can you help me make up something to understand when a team has a match less than the others so that in the cell i would not take 0 as a value or maybe i take a message in the cell like FALSE or something?
is it possible?
thanks a lot for your help

 

[Andrew Poulsom]

Wouldn't it be easier (especially for me) not to use zero in the ID column?

 

[gr_zaki]

For one more time you are right but i haven't explained what The ID stands for because i thought i wasn't nessesary that time. The ID stands for WIN or LOOSE & Score & Goals Against e.t.c and a Couple of things that involved in the game played bassed on a type created by me. so if a team looses it takes 0 i can't change it. Thats why i am searching for something else.. i have thoughts of changing that into 1 for win 2 for loose X for draw. so i did it based again in the type you gave me but i encountered a problem with "X" so instead of "X" i decided to put 0 everything when ok but i still have the same problem. the problem that i mentioned in my previous message. can you help?
Thanks for all your help you provided so far

 

[Andrew Poulsom]

Ok, you win!

FootballLeague.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Home	Away	1	2	ID	H	A	HOME	MATCH_1	MATCH_2	MATCH_3	MATCH_4
2	10/01/2002	Panahaiki	Aris	0	1	5	1	1	Panahaiki	5	2
3	10/01/2002	Aigaleo	AEK	1	1	6	1	1	AEK	1
4	10/01/2002	Iraklis	OFI	3	0	8	1	1	AWAY	MATCH_1	MATCH_2	MATCH_3	MATCH_4
5	10/01/2002	Panionios	Ionikos	2	0	4	1	1	ARIS	5
6	10/01/2002	Akratitos	Xanthi	0	1	5	1	1	Giannina	6	1
7	10/01/2002	Proodeytiki	Giannina	1	0	6	1	1
8	10/01/2002	Olympiakos	Kallithea	1	0	5	1	1
9	10/01/2002	PAOK	Panathinaikos	4	1	5	1	1
10	11/01/2002	Aris	Panionios	1	0	1	1	1
11	1/25/2002	Panahaiki	Iraklis	0	2	2	2	1
Sheet1

 

[gr_zaki]

THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU!
t h a n k Y O U !!!!!

 

[gr_zaki]

i know this situation is getting out of hands )) but i still need your opinion and once you are a "doctor" in your kind
i am going to say it
well i made the table perfect! thanks to you but the application became extremely slow.. then i thought that i can copy and then paste special only the values of the matches that have taken place until now but the other ones with "-" what can i do? is there anything? please doctor my processor need your help

 

[Andrew Poulsom]

I could do it with some VBA code. Would that be OK?

 

[gr_zaki]

yes it's ok sorry for not answearing quick but my work doesn't live me free time
i apologise

 

[Andrew Poulsom]

This will convert formulas to values if the match has been played:

Sub Test()
    Dim Rng As Range
    Dim c As Range
    On Error GoTo NoFormulas
'   *** Change range references to suit ***
    Set Rng = Union(Range("J2:M17"), Range("J19:M34")).SpecialCells(xlCellTypeFormulas)
    For Each c In Rng
        If c.Value <> "" Then
            c.Value = c.Value
        End If
    Next c
NoFormulas:
End Sub

Another option to speed up your data entry process is to set calculation to manual (Tools, Options, Calculation tab). Then press F9 to update the results of your formulas. "Calculate" will appear in the Status Bar to indicate the need to do this.