Re: Html Parsing by stepping some strings to set records side by side ( in total six
Hello,
I attach an Example file where to set macros or load what I tried to explicate three days ago.
http://wikisend.com/download/211346/Example.xlsm
Nice to reach a solution.
Regards,
frank.
Hello everyone,
I will be patient and very grateful to who will assist me to solve my problem.
With the following code, I jump absent results (those which are "-").
I get the TOTAL of goals scored and had, so also the TOTAL victories, defeats or draws of the LAST 6 GAMES of a team.
How do I get the results individually (goals scored and had, victory, draw and/or defeat) SINGLE DAY for single day, always in the LAST SIX games played and always JUMPING THE RESULTS eguals to "-" ?
Sub Home_in_Home_Form_Last_6_Total()
Dim url1 As String
Dim Text1 As String
Dim http1 As Object
Dim sControllo1 As String, sControllo2 As String, sControllo3 As String, sControllo_ID As String
Dim sLen1 As String, sLen2 As String, sLen3 As String, sLenID As String
Dim Posiz1 As Long, Posiz2 As Long, Posiz3 As Long, Posiz4 As Long, Posiz5 As Long
Dim Posiz6 As Long, Posiz7 As Long, Posiz8 As Long, Posiz9 As Long
Dim nRigaInizio As Integer
Dim nStartColumn As Integer
Dim nRigaTabella As Integer
Dim IRange As Range, myRange As Range
Application.ScreenUpdating = False 'BLOCCO SCHERMO SU SVILUPPO ROUTINE
' =====================================================
For i = 4 To LastRowOfTheActiveSheet
If Range("M" & i).Value < 6 Then GoTo 2 'n° HOME TOTAL PLAYED GAMES
nStartColumn = 693 'nStartColumn To Set Records
sConta = 0
Posiz1 = 1
sW = 0
sD = 0
sL = 0
sGf = 0
sGs = 0
url1 = "http://betonews.com/popup.asp?lang=en&tp=9011&idt=717&tqw=1&tqc=-1&tqy=0&tqt=20"
Set http1 = CreateObject("MSXML2.XMLHTTP")
http1.Open "POST", url1, False
Do
On Error Resume Next
http1.send
Loop Until http1.readyState = 4
DoEvents
On Error GoTo 0
Text1 = http1.responseText
' ====================================================
sControllo2 = "<span class=tbl_"
sLen2 = Len(sControllo2)
sControllo3 = "_txt>"
sLen3 = Len(sControllo3)
sControllo4 = "</span></td>"
sLen4 = Len(sControllo4)
Text1 = Replace(Text1, Chr(34), "")
' verifico che la stinga contenga i dati richiesti
If InStr(1, Text1, sControllo2) = 0 Then GoTo 2
Do
If InStr(Posiz1, Text1, sControllo2) = 0 Then Exit Do
'Posiz1 = 1
'Posiz1.1 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.2 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.3 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.4 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.5 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.6 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.7 = InStr(Posiz1, Text1, sControllo2) + sLen2
'Posiz1.8 = InStr(Posiz1, Text1, sControllo2) + sLen2
Posiz1 = InStr((InStr((InStr((InStr((InStr((InStr((InStr((InStr(Posiz1, Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2), Text1, sControllo2) + sLen2
Posiz2 = InStr(Posiz1, Text1, sControllo3) + sLen3 'Posiz1 + n° 7 Posiz1
Posiz3 = InStr(Posiz2, Text1, sControllo4)
'sAway = Trim(Mid(Text1, Posiz2, Posiz3 - Posiz2))
sResult = Trim(Mid(Text1, Posiz2, Posiz3 - Posiz2))
'If sAway <> sVerifica Then GoTo 1
If sResult = "-" Then GoTo 1
Dim sPrimoGoal As Single
Dim sSecondoGoal As Single
divide = Split(sResult, "-")
'On Error Resume Next
sPrimoGoal = divide(o)
sSecondoGoal = divide(1)
' conteggio W D L e Gf Gs
If sPrimoGoal = sSecondoGoal Then sD = sD + 1
If sPrimoGoal > sSecondoGoal Then sW = sW + 1
If sPrimoGoal < sSecondoGoal Then sL = sL + 1
sGf = sGf + sPrimoGoal
sGs = sGs + sSecondoGoal
sConta = sConta + 1 'we need of a counter
'Cells(j, nStartColumn + 1).Value = CDbl(sPrimoGoal)
'Cells(j, nStartColumn + 2).Value = CDbl(sSecondoGoal)
'If sConta >= Range("M" & i).Value And sConta <= 10 Then Exit Do
If sConta > 5 Then Exit Do
'Here we find the last sixth game of which we need
'so when > 5 ... sConta stop itself why it reaches the 6th game played without Results egual to " - "
1:
Posiz1 = Posiz3 + 1
Loop
'after loop in_columns
Cells(i, nStartColumn + 1).Value = CDbl(sW)
Cells(i, nStartColumn + 2).Value = CDbl(sD)
Cells(i, nStartColumn + 3).Value = CDbl(sL)
Cells(i, nStartColumn + 4).Value = CDbl(sGf)
Cells(i, nStartColumn + 5).Value = CDbl(sGs)
2:
Application.StatusBar = "Elaborazione dati : " & i - 3 & " di " & LastRowOfTheActiveSheet - 3 & ""
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("A1").Select
End Sub
Who will assist me can try and make me appear in order the results, goals, wins, draws and defeats ....
in order, day to day, in their total 6 last games?
Thank's so much to everyone who will read me.
I await for You dears.
Frank.
I get stuck in the routine...
here i am and i write as follow:
if you open the file it doesn't crash cause macros are disabled so you can open vba with alt + f11
Sheet Elaborazioni - Sheet 1
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = Range("E1").Column Or Target.Column = Range("F1").Column Then
UserForm1.Show
End If
End SubUserform
Private Sub UserForm_Terminate()
ActiveCell.Value = ComboBox1.Value
End SubMy woorkbook an all the others sheet: " Archivio - Studies - Spiegazione " have
Option explicitModulo 1 where i have several functions... for example:
Function comb_x1(c As Range) As Double
'Dim parziale As Double
'Dim i As Integer
'Dim j As Integer
'Dim k As Integer
'Dim l As Integer
'Dim m As Integer
'Dim n As Integer
'Dim o As Integer
'Dim p As Integer
'Dim q As Integer
'Dim r As Integer
'Dim s As Integer
'Dim t As Integer
'Dim u As Integer
'Dim v As Integer
'Dim w As Integer
'Dim x As Integer
'Dim y As Integer
'Dim z As Integer
'Dim aa As Integer
'Dim ab As Integer
'Dim ac As Integer
'Dim ad As Integer
'Dim ae As Integer
'Dim af As Integer
'Dim ag As Integer
'Dim ah As Integer
'Dim ai As Integer
'Dim aj As Integer
'Dim ak As Integer
'Dim al As Integer
'Dim NRighe As Integer
'Dim NColonne As Integer
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 1 Then
comb_x1 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value
Next
comb_x1 = parziale
End Function
Function comb_x2(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 2 Then
comb_x2 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 1
For j = i + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value
Next
Next
comb_x2 = parziale
End Function
Function comb_x3(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 3 Then
comb_x3 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 2
For j = i + 1 To NRighe - 1
For k = j + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value
Next
Next
Next
comb_x3 = parziale
End Function
Function comb_x23(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 23 Then
comb_x23 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 22
For j = i + 1 To NRighe - 21
For k = j + 1 To NRighe - 20
For l = k + 1 To NRighe - 19
For m = l + 1 To NRighe - 18
For n = m + 1 To NRighe - 17
For o = n + 1 To NRighe - 16
For p = o + 1 To NRighe - 15
For q = p + 1 To NRighe - 14
For r = q + 1 To NRighe - 13
For s = r + 1 To NRighe - 12
For t = s + 1 To NRighe - 11
For u = t + 1 To NRighe - 10
For v = u + 1 To NRighe - 9
For w = v + 1 To NRighe - 8
For x = w + 1 To NRighe - 7
For y = x + 1 To NRighe - 6
For Z = y + 1 To NRighe - 5
For aa = Z + 1 To NRighe - 4
For ab = aa + 1 To NRighe - 3
For ac = ab + 1 To NRighe - 2
For ad = ac + 1 To NRighe - 1
For ae = ad + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value * c.Cells(o, 1).Value * c.Cells(p, 1).Value * c.Cells(q, 1).Value * c.Cells(r, 1).Value * c.Cells(s, 1).Value * c.Cells(t, 1).Value * c.Cells(u, 1).Value * c.Cells(v, 1).Value * c.Cells(w, 1).Value * c.Cells(x, 1).Value * c.Cells(y, 1).Value * c.Cells(Z, 1).Value * c.Cells(aa, 1).Value * c.Cells(ab, 1).Value * c.Cells(ac, 1).Value * c.Cells(ad, 1).Value * c.Cells(ae, 1).Value
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
comb_x23 = parziale
End FunctionThis is it all dear.
i hope you will find a solution.
see u.
thank's for all your time.
kind regards.
frank
Re: I get stuck in the routine...
Hi dear PIKE,
Thank you for your interest and your response.
In practice:
When I open the worksheet ...
... in Elaborazioni sheet...
I click on G4 to select a choice of game and the CPU goes into overload and crashes me running and everything ... this data validation in G:G is linked to N:N e O:O rules;
I wrote a series of combinatorial code ... If you open Vba you will find them in MODULE 1
these are invoked in P:P e Q:Q... in ELABORAZIONI sheet...
I don't understand the problem ... will be a userform problem???
There will be a way to lighten these Combinatorial processes?
Thank you for your cooperation and for all the time that You give me.
Yours sincerely,
frank.
Hello everyone.
I ask an opinion:
is it possible that I get stuck in the routine of combinatorics "sheet 1 called elaborations" "??
[Blocked Image: http://i58.tinypic.com/311vo06.jpg]
who could give me an help?
thank you very much.
frank
excel version 2010
Re: Last 6 matches home and away soccer form table
Tx deaar.
I will let you know about.
Tx for your time again and again.
Take care.
Regards
Re: Last 6 matches home and away soccer form table
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))
Hi dear,
Tx so much, i m using this one for english conference.
Tx for ur support
Re: Last 6 matches home and away soccer form table
Hi dear txx.
Chiaro.
Please,
How can i sum the scored goals (in k:k) of a home team (d:d) for the last six occurrences ???
Tx 4 your time.
Take care
Re: Last 6 matches home and away soccer form table
hi dear i'm alive 
how can i set your formula as written down with MATR.SOMMA.PRODOTTO???
for the last six occurrences starting from the top, the beginning rows???
... like wrote by you some day ago...
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))
tx of all.
take care.
frank
Re: Last 6 matches home and away soccer form table
hi dear,
tx so much for this other helpfull info.
i established all my needed functions about your big golden infos...
i'll let me to set some functions in vba code, to resize the file at the minimum!!!
please, can i ask something about vba codes in these days???
what do you suggest me about ???
i want to obtain only calculated values, by clearing formulas and functions inserted in any cells, can i try a method to keep formulas and translate them in vba...??? are there any functions???
if you can't... i'll move to post it by opening a new thread.
thank's for all you have done for me.
take care.
your,
regards.
Re: Last 6 matches home and away soccer form table
HI DEAR,
i establish this formula, but i didn't understand
Quote=CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;K:K;">0")
QuoteThis formula looks for "Italy" in column A, then takes the number in column B just after the work (it knows now this is the last round of the series) and subtracts 5 from that, so it knows the last 6 rounds... and it calculates from there. Now you don't have to adjust ranges, just change the text "Italy" when you apply formula somewhere else.
By trying this i fall in a calculation tha returns me " 0 " ???
Quote
B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;
i didn't understand " index" .... what i might to do???
thank's for all, as soon as for an helpfully reply.
take care.
luciano
Re: Last 6 matches home and away soccer form table
Please If you can i have the last true request,
how can establish in near columns, for each team in y:y,
their last sequences, in their last six total occurences, by not ordering them about home or away games???
Last sequences about:
win draw lost games
U1 O1 games
U2 O2 games
U3 O3 games
BTTS ... NO BTTS games
i'm off topic... i'll search for by posting a new thread.
Thanks 4 all BOSS
Luciano.
Re: Last 6 matches home and away soccer form table
What to say Boss... you satisfy me on all my answers, doubts and curiosites.
I have to leave and use the firsts above arrays formulas...
" because of in A3735 there's a 42nd adavanced round played game....
In A3722 there's 22nd round replayed games... covering last six occurrences. "
The written orders let me in error... as soon as for many leagues, english expecially.
Well i ll put the last formulas you gave me... where i can.
I don t know how to thanks for all your time you spent about my unknownledges.
See you BOSS!!! Great teacher.
You was the SOLUTION!!!
Nice to meet you.
Take care 4eva.
Kind regards.
Re: Last 6 matches home and away soccer form table
Yes this last is an option that i appreciate;
If i don't use ab$2 and ac$2... and i abandon the sumproduct array idea...
Are there other ways???
There is k$3; k$100;"> 0" to calculate if score
I thought about ((k$1:k$100)+(L$1:L$100));"<=1" to calculate under 1,5...
But i fall in error...
I would clear NOQRST columns
Thanks 4 your time Boss.
Take care
Re: Last 6 matches home and away soccer form table
Quote from NBVC;699250Display MoreHere is another option where you can use whole column references like D:D so you don't have to limit the ranges (in the case that they move around).
If you put the country in column A just before their series to identify the series, e.g. type Italy (or Italia) in A1, England (or Inghilterra) in A2309 (just before the England games)
Then you can use this formula in Z3:
=CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;K:K;">0")
This formula looks for "Italy" in column A, then takes the number in column B just after the work (it knows now this is the last round of the series) and subtracts 5 from that, so it knows the last 6 rounds... and it calculates from there. Now you don't have to adjust ranges, just change the text "Italy" when you apply formula somewhere else.
Hopefully that might help.
Another big solution.
What a king you are!!
You are doping me.
Re: Last 6 matches home and away soccer form table
Thanks dear,
I understood.
You simple the BEST.
By rounds counting in B we can set countif formulas, about last six occurences, basing them on :
> =MAX (B$1:B$100) - 6
Now how to sum the scored goals in K & L ??? To calculate under over 1.5; 2.5; 3.5 and how calculate both teams score and not ???
Thanks so much.
Simple a Genius.
Regards
Re: Last 6 matches home and away soccer form table
Dear,
the original formulas, under form of arrays, that you quoted above are working, so now i would translate them in simple formulas without under form of arrays.
Exscusme for the mutiple posts, i believe in you and i'll follow only you.
I hope you understand.
Nice to wait for You.
Regards
Re: Last 6 matches home and away soccer form table
Quote from NBVC;699218Display MoreIn fact, if you can set fixed ranges to cover only those last 6 rounds e.g. rows 3 to 67 only, then you can simplify with COUNTIFS
In Z3 the formula (in Italian) would now simply be:
=CONTA.PIÙ.SE($D$3:$D$67;Y3;$K$3:$K$67;">0")
In AA3
=CONTA.PIÙ.SE($D$3:$D$67;Y3;$K$3:$K$67;"0")
in AK3
=CONTA.PIÙ.SE($F$3:$F$67;Y3;$L$3:$L$67;">0")
and in AL3:
=CONTA.PIÙ.SE($F$3:$F$67;Y3;$L$3:$L$67;"0")
Dear friend, about ONLY LAST SIX LEAGUE ROUNDS...
For example in Italy Serie A league championship, like in column B,from the first last round, the 19th, until the sixth last round, the 14th round…by covering and taking always the last six occurences…Z9: Inter played _4 games in home_ by scoring 4 times, like shown in Z:Z … ( 4 scored Yes)
Z9: Inter played _2 games away_ by scoring only 1 time and not scoring also 1 time ( 1 scored Yes & 1 Not)By following these rules I want calculate what I explained, about all the observable league teams in Y:Y
Note Well:In other leagues, expecially English leagues, when I refresh query web about new league championship results, I find postponed or abandoned games of previous league rounds which are replayed.These replayed games are assorted, mixed to very last 6 played rounds…
According to my ideas They are calculated like lasts by me… and sometimes, by counting last six occurences of a Team, covering last six league rounds, we should find a Team which played also 5 times like Home and only 1 Away.
As follows, see in A5027... ENGLISH CONFERENCE...
THERE ARE THREE REPLAYED GAMES THAT I COUNT LIKE LAST SIX OCCURENCES...
SO RAWS CHANGES... AND I MISTAKE CALCULATION... WITH HIDE RAWS..
How can count these as follows... with countif???
In Z2:=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))
in AA2:=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870=0);1))))
In AK2:=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($F$3:$F$870=Y3)*($L$3:$L$870>0);1))))
in AL2:=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($F$3:$F$870=Y3)*($L$3:$L$870=0);1))))
Thanks 4 all u ll do.
Take care.
Regards
Re: Last 6 matches home and away soccer form table
Quote from FrankieBue;699088Display MoreYou are a BOMBBBBBBBBBBBBBBBBBBBBBBBBBB!!!!!!!!!
SOLVED
SOLVED
SOLVED
SOLVED
so now i let me to try the other statistics about columns q r s t ... by taking your formulas!!!
nice to meet you deaaar
muy feliz.
kind regardsss
Hello my dear friend,
making even thanks to you for what You have suggested to me,
I have to ask:
deleting the calculations in columns NOQRST. ..
to lighten the sheet ...
because these calculations appear to me already in the cells of calculation from Z to CL ...
Can I calculate formulas without my calculations in the form of matrices??
I could help with the cells help? What do you suggest?
Could you give me more advices or straights???
Thank you for everything you have done and will do ...
look forward to hearing from you...
take care;
regards,
luciano.
Re: Last 6 matches home and away soccer form table
Quote from NBVC;699082Display MoreLet's try this again....
In Z2:
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))
in AA2:
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870=0);1))))
in AK2:
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($F$3:$F$870=Y3)*($L$3:$L$870>0);1))))
in AL2:
=SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($F$3:$F$870=Y3)*($L$3:$L$870=0);1))))
each formula must be confirmed with CTRL+SHIFT+ENTER keys, so that { } appear around the formulas:
then you can copy each down.
You are a BOMBBBBBBBBBBBBBBBBBBBBBBBBBB!!!!!!!!!
SOLVED
SOLVED
SOLVED
SOLVED
so now i let me to try the other statistics about columns q r s t ... by taking your formulas!!!
nice to meet you deaaar
muy feliz.
kind regardsss