Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Errore VBA per inserimento formula matriciale
240 views
Skip to first unread message
casanmaner
Jan 23, 2016, 5:35:36 AM1/23/16
to
Salve ragazzi vorrei capire in cosa sbaglio e se c'è soluzione.
Ho un intervallo di celle che contengono la seguente formula matriciale:
=SE.ERRORE(INDICE(ElencoClienti;CONFRONTA(PICCOLO(SE(SE.ERRORE(CONFRONTA(ElencoClienti;ElencoClienti;0);"")=RIF.RIGA(ElencoClienti)-(RIF.RIGA($B$5)-RIF.RIGA($B$1));1)*CONTA.SE(ElencoClienti;"<="&ElencoClienti);RIF.RIGA(ElencoClienti)-(RIF.RIGA($B$5)-RIF.RIGA($B$1))+RIGHE(ElencoClienti)-SOMMA(SE.ERRORE(1/CONTA.SE(ElencoClienti;ElencoClienti);"")));CONTA.SE(ElencoClienti;"<="&ElencoClienti);));"")
(che altro non è che una "rielaborazione" della formula che consente di ordinare, dando risultati univoci, un elenco di valori di testo)
Per inserirla in prima battuta la inserisco nella prima cella. Poi seleziono il numero di celle per le quali desidero sia ripetuta e poi F2 e Ctrl+Maisc+Invio.
Ho registrato questa operazione con il registratore ed ottengo ad es:
Range("I5:I104").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(ElencoClienti,MATCH(SMALL(IF(IFERROR(MATCH(ElencoClienti,ElencoClienti,0),"""")=ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2)),1)*COUNTIF(ElencoClienti,""<=""&ElencoClienti),ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2))+ROWS(ElencoClienti)-SUM(IFERROR(1/COUNTIF(ElencoClienti,ElencoClienti),""""))),COUNTIF(ElencoClienti,""<=""&ElencoClienti),)),"""")"
Se però poi vado ad eseguire il comando viene dato il messaggio di errore:
Errore di run-time '1004':
Impossibile impostare la proprietà FormulaArray per la classe Range.
C'è modo di riuscire tramite VBA ad inserire questa particolare formula matriciale?
Grazie e ciao
Ho un intervallo di celle che contengono la seguente formula matriciale:
=SE.ERRORE(INDICE(ElencoClienti;CONFRONTA(PICCOLO(SE(SE.ERRORE(CONFRONTA(ElencoClienti;ElencoClienti;0);"")=RIF.RIGA(ElencoClienti)-(RIF.RIGA($B$5)-RIF.RIGA($B$1));1)*CONTA.SE(ElencoClienti;"<="&ElencoClienti);RIF.RIGA(ElencoClienti)-(RIF.RIGA($B$5)-RIF.RIGA($B$1))+RIGHE(ElencoClienti)-SOMMA(SE.ERRORE(1/CONTA.SE(ElencoClienti;ElencoClienti);"")));CONTA.SE(ElencoClienti;"<="&ElencoClienti);));"")
(che altro non è che una "rielaborazione" della formula che consente di ordinare, dando risultati univoci, un elenco di valori di testo)
Per inserirla in prima battuta la inserisco nella prima cella. Poi seleziono il numero di celle per le quali desidero sia ripetuta e poi F2 e Ctrl+Maisc+Invio.
Ho registrato questa operazione con il registratore ed ottengo ad es:
Range("I5:I104").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(ElencoClienti,MATCH(SMALL(IF(IFERROR(MATCH(ElencoClienti,ElencoClienti,0),"""")=ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2)),1)*COUNTIF(ElencoClienti,""<=""&ElencoClienti),ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2))+ROWS(ElencoClienti)-SUM(IFERROR(1/COUNTIF(ElencoClienti,ElencoClienti),""""))),COUNTIF(ElencoClienti,""<=""&ElencoClienti),)),"""")"
Se però poi vado ad eseguire il comando viene dato il messaggio di errore:
Errore di run-time '1004':
Impossibile impostare la proprietà FormulaArray per la classe Range.
C'è modo di riuscire tramite VBA ad inserire questa particolare formula matriciale?
Grazie e ciao
Norman Jones
Jan 23, 2016, 6:30:41 AM1/23/16
to
Norman Jones
Jan 23, 2016, 6:33:30 AM1/23/16
to
On 23/01/2016 11:30, Norman Jones wrote:
> Ciao Casanmaner,
>
> Vedi:
> https://support.microsoft.com/it-it/kb/213181
Vedi anche:
> Ciao Casanmaner,
>
> Vedi:
> https://support.microsoft.com/it-it/kb/213181
https://support.microsoft.com/it-it/kb/213841
===
Regards,
Norman
casanmaner
Jan 23, 2016, 7:06:43 AM1/23/16
to
Allora mi sa che non c'è molto da fare.
casanmaner
Jan 23, 2016, 7:15:01 AM1/23/16
to
Ho sostituito il nome dell'intervallo "ElencoClienti" con "_EC" stando così sotto i 255 caratteri.
grazie ancora
ciao
casanmaner
Jan 23, 2016, 12:47:20 PM1/23/16
to
=SE.ERRORE(INDICE(_EC;CONFRONTA(PICCOLO(SE(SE.ERRORE(CONFRONTA(_EC;_EC;);1)=RIF.RIGA(INDIRETTO("1:"&RIGHE(_EC)));CONTA.SE(_EC;"<="&_EC));RIF.RIGA(A1));CONTA.SE(_EC;"<="&_EC);));"")
Norman Jones
Jan 24, 2016, 6:05:12 AM1/24/16
to
>>> Grazie Norman.
>>> Allora mi sa che non c'è molto da fare.
>>
>> E invece no :-)
>> Ho sostituito il nome dell'intervallo "ElencoClienti" con "_EC"
>>stando così sotto i 255 caratteri.
>> grazie ancora
>> ciao
>
> Aggiungo che visto che la precedente formula dava qualche problema,
> ancora non sono riuscito a capire perché, quando riducevo il numero
> di righe dell'intervallo cercando un po' ho sostituito con questa versione:
> =SE.ERRORE(INDICE(_EC;CONFRONTA(PICCOLO(SE(SE.ERRORE(CONFRONTA(_EC;_EC;);1)=
> RIF.RIGA(INDIRETTO("1:"&RIGHE(_EC)));CONTA.SE(_EC;"<="&_EC));RIF.RIGA(A1));CONTA.SE(_EC;"<="&_EC);));"")
Infatti, non avevo provato la tua formula in quanto, chiaramente, il
problema che avevi riscontrato si risiedeva altrove.
Tuttavia, rivolgendosi a la tua 'workaround', ovvero la sostituzione del
nome "ElencoClienti" con "_EC" per ridurre la lunghezza dell'espressione
al di sotto del limite di 255 caratteri, preferirei la soluzione
standard, che prevede la suddivisione dell'espressione in più stringhe.
In questo modo, tra altre cose, credo che si aumenti la leggibilità e la
comprensibilità sia della formula che il codice. Inoltre, non avrai
sempre la bella fortuna di poter abbreviare un definito nome!
Quindi,in linea di massima, preferirei qualcosa del genere:
'=========>>
Public Sub Tester()
Dim s1 As String, s2 As String, s3 As String
Dim s4 As String, s5 As String, s6 As String
s1 = _
"=IFERROR(INDEX(ElencoClienti,MATCH(SMALL(IF(IFERROR(MATCH(ElencoClienti"
s2 = _
",ElencoClienti,0),"""")=ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2)),1)*"
s3 = _
"COUNTIF(ElencoClienti,""<=""&ElencoClienti),ROW(ElencoClienti)-(ROW"
s4 = _
"(R5C2)-ROW(R1C2))+ROWS(ElencoClienti)-SUM(IFERROR(1/COUNTIF("
s5 = _
"ElencoClienti,ElencoClienti),""""))),COUNTIF(ElencoClienti,""<=""&"
s6 = _
"ElencoClienti),)),"""")"
Selection.Formula = s1 & s2 & s3 & s4 & s5 & s6
End Sub
'<<=========
===
Regards,
Norman
casanmaner
Jan 24, 2016, 8:01:20 AM1/24/16
to
A dire il vero, poiché avevo letto gli articoli che mi avevi linkato, avevo provato a suddividere il testo della formula ma dava comunque errore.
Adesso provando la tua se l'inserminto è tramite "Formula" funziona e non dà errore.
Ma se, come ho necessità, cerco l'inserimento come "FormulaArray" invece viene dato errore.
A te no?
Ho semplicemente sostituito con: Selection.FormulaArray = s1 & ....
ciao
Norman Jones
Jan 24, 2016, 8:55:47 PM1/24/16
to
Ciao Casanmaner,
> A dire il vero, poiché avevo letto gli articoli che mi avevi linkato, avevo provato a suddividere il testo della formula ma dava comunque errore.
> Adesso provando la tua se l'inserminto è tramite "Formula" funziona e non dà errore.
> Ma se, come ho necessità, cerco l'inserimento come "FormulaArray" invece viene dato errore.
> A te no?
> Ho semplicemente sostituito con: Selection.FormulaArray = s1 & ....
A me il seguente appeoccio funziona senza problema:
'=========>>
Option Explicit
'--------->>
Public Sub Demo()
Dim s1 As String, s2 As String
Const sStr As String = "ElencoClienti"
Const sStr2 As String = "_EC"
s1 = "=IFERROR(INDEX(ElencoClienti,MATCH(SMALL(IF(IFERROR(MATCH(" _
& "ElencoClienti,ElencoClienti,0),"""")=ROW(ElencoClienti)-" _
& "(ROW(R5C2)-ROW(R1C2)),1)*COUNTIF(ElencoClienti,""<=""&" _
& "ElencoClienti),ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2))+" _
& "ROWS(ElencoClienti)-SUM(IFERROR(1/COUNTIF(ElencoClienti," _
& "ElencoClienti),""""))),COUNTIF(ElencoClienti,""<=""&" _
& "ElencoClienti),)),"""")"
s2 = Replace(s1, sStr, sStr2)
With ActiveSheet.Range("E2:K7")
.FormulaArray = s2
.Replace sStr2, sStr
End With
End Sub
'<<=========
Un vantaggio incidentale di questo approcchio è che non sia necessario
modificare il nome definito o alcun testo della formula.
Potresti scaricare il mio file di prova Casanmaner20160125.xlsm a:
http://1drv.ms/1OQICEU
Nota che questo file non è destinato a rappresentare un file funzionale.
In realtà non è altro che una dimostrazione del inserimento da VBA della
tua lunga formula matriciale.
===
Regards,
Norman
> A dire il vero, poiché avevo letto gli articoli che mi avevi linkato, avevo provato a suddividere il testo della formula ma dava comunque errore.
> Adesso provando la tua se l'inserminto è tramite "Formula" funziona e non dà errore.
> Ma se, come ho necessità, cerco l'inserimento come "FormulaArray" invece viene dato errore.
> A te no?
> Ho semplicemente sostituito con: Selection.FormulaArray = s1 & ....
'=========>>
Option Explicit
'--------->>
Public Sub Demo()
Dim s1 As String, s2 As String
Const sStr2 As String = "_EC"
s1 = "=IFERROR(INDEX(ElencoClienti,MATCH(SMALL(IF(IFERROR(MATCH(" _
& "ElencoClienti,ElencoClienti,0),"""")=ROW(ElencoClienti)-" _
& "(ROW(R5C2)-ROW(R1C2)),1)*COUNTIF(ElencoClienti,""<=""&" _
& "ElencoClienti),ROW(ElencoClienti)-(ROW(R5C2)-ROW(R1C2))+" _
& "ROWS(ElencoClienti)-SUM(IFERROR(1/COUNTIF(ElencoClienti," _
& "ElencoClienti),""""))),COUNTIF(ElencoClienti,""<=""&" _
& "ElencoClienti),)),"""")"
s2 = Replace(s1, sStr, sStr2)
With ActiveSheet.Range("E2:K7")
.FormulaArray = s2
.Replace sStr2, sStr
End With
End Sub
'<<=========
Un vantaggio incidentale di questo approcchio è che non sia necessario
modificare il nome definito o alcun testo della formula.
Potresti scaricare il mio file di prova Casanmaner20160125.xlsm a:
http://1drv.ms/1OQICEU
Nota che questo file non è destinato a rappresentare un file funzionale.
In realtà non è altro che una dimostrazione del inserimento da VBA della
tua lunga formula matriciale.
===
Regards,
Norman
Norman Jones
Jan 24, 2016, 9:54:03 PM1/24/16
to
On 25/01/2016 01:55, Norman Jones wrote:
> .Replace sStr2, sStr
Preferibile e più resistente sarebbe:
.Replace What:=sStr2, Replacement:=sStr, Lookat:=xlPart
Ho aggiornato il mio file.
===
Regards,
Norman
> .Replace sStr2, sStr
Preferibile e più resistente sarebbe:
.Replace What:=sStr2, Replacement:=sStr, Lookat:=xlPart
Ho aggiornato il mio file.
===
Regards,
Norman
casanmaner
Jan 25, 2016, 3:33:57 AM1/25/16
to
Ho inteso lo stratagemma.
In pratica tu inserisci la formula matriciale con un riferimento ridotto, anche se non esistente, in modo che il numero di caratteri sia inferiore ai 255 e poi nella formula vai a sostituire il riferimento con il nome dell'intervallo esistente. In questo modo la formula rimane "matriciale" ma con i riferimenti corretti.
Però in futuro il problema potrebbe rimanere nel caso in cui, anche con un riferimento molto ridotto, il numero di caratteri superasse i 235.
Mi pare di capire che la semplice "suddivisione" in stringhe minori di 235 caratteri comunque non venga "digerita" dall'inserimento tramite VBA della FormulaArray.
ciao
Norman Jones
Jan 25, 2016, 5:52:42 AM1/25/16
to
Ciao Casanmaner,
> Però in futuro il problema potrebbe rimanere nel caso in cui, anche
> con un riferimento molto ridotto, il numero di caratteri superasse i
> 235.
Infatti, credo che il limito sia 255 caratteri anzichè 235.
> Mi pare di capire che la semplice "suddivisione" in stringhe minori di
> 235 caratteri comunque non venga "digerita" dall'inserimento tramite
> VBA della FormulaArray.
Io non vedo un problema. Tuttavia, non è spesso che io utilizzo delle
formule, siano maticiale che non matriciali, con più di 510 caratteri.
Comunque, ti chiederei gentilmente di caricare un file con una formula
di questa genere, per permettermi di provarlo.
===
Regards,
Norman
> Però in futuro il problema potrebbe rimanere nel caso in cui, anche
> con un riferimento molto ridotto, il numero di caratteri superasse i
> 235.
> Mi pare di capire che la semplice "suddivisione" in stringhe minori di
> 235 caratteri comunque non venga "digerita" dall'inserimento tramite
> VBA della FormulaArray.
formule, siano maticiale che non matriciali, con più di 510 caratteri.
Comunque, ti chiederei gentilmente di caricare un file con una formula
di questa genere, per permettermi di provarlo.
===
Regards,
Norman
Norman Jones
Jan 25, 2016, 5:57:18 AM1/25/16
to
On 25/01/2016 08:33, casanmaner wrote:
> Ho inteso lo stratagemma.
> In pratica tu inserisci la formula matriciale con un riferimento ridotto,
> anche se non esistente, in modo che il numero di caratteri sia inferiore
>ai 255 e poi nella formula vai a sostituire il riferimento con il nome
> dell'intervallo esistente. In questo modo la formula rimane "matriciale"
> ma con i riferimenti corretti.
>
> Però in futuro il problema potrebbe rimanere nel caso in cui, anche con
> un riferimento molto ridotto, il numero di caratteri superasse i 235.
> Mi pare di capire che la semplice "suddivisione" in stringhe minori di
> 235 caratteri comunque non venga "digerita" dall'inserimento tramite VBA
> della FormulaArray.
> Ho inteso lo stratagemma.
> In pratica tu inserisci la formula matriciale con un riferimento ridotto,
> anche se non esistente, in modo che il numero di caratteri sia inferiore
>ai 255 e poi nella formula vai a sostituire il riferimento con il nome
> dell'intervallo esistente. In questo modo la formula rimane "matriciale"
> ma con i riferimenti corretti.
>
> Però in futuro il problema potrebbe rimanere nel caso in cui, anche con
> un riferimento molto ridotto, il numero di caratteri superasse i 235.
> Mi pare di capire che la semplice "suddivisione" in stringhe minori di
> 235 caratteri comunque non venga "digerita" dall'inserimento tramite VBA
> della FormulaArray.
casanmaner
Jan 25, 2016, 6:15:50 AM1/25/16
to
Questo è il file:
https://www.dropbox.com/s/dlmmsvqw04y25zm/Dati%20SdS%20YK05U%20%28commercialisti%29%202016.xlsm?dl=0
Nel modulo M3_GestioneRighe trovarai la routine InserisciFormule.
Attualmente il testo della formula è con il riferimento all'intervallo _EC.
casanmaner
Jan 25, 2016, 9:45:57 AM1/25/16
to
Ciao Norman, continuando la discussione sull'inserimento di formule con oltre 255 caratteri ad es. se trasformassi la precedente in modo che la stessa sia utilizzabile anche con Excel2003 (non utilizzando quindi la funzione IFERROR) dovrei scriverla così:
"=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,""<=""&_EC),)))"
In questo caso, anche utilizzando il riferimento all'intervallo di celle con un nome breve come "_EC" comunque il testo nel suo complesso supera i 255 caratteri e anche lo stratagemma della sostituzione, a me pare, non funzionerebbe in quanto la sostituzione dovrebbe avvenire anche parti di testo che rappresenta i nomi delle varie funzioni, che poi non verrebbero tradotte in Italiano.
Ma magari tu hai una soluzione anche per questo caso :-)
Io intanto ci studio un po' su perché il foglio dovrebbe essere utilizzato anche da Excel 2003 e la funzione IFERROR (SE.ERRORE in italiano) non è disponibile.
"=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,""<=""&_EC),)))"
In questo caso, anche utilizzando il riferimento all'intervallo di celle con un nome breve come "_EC" comunque il testo nel suo complesso supera i 255 caratteri e anche lo stratagemma della sostituzione, a me pare, non funzionerebbe in quanto la sostituzione dovrebbe avvenire anche parti di testo che rappresenta i nomi delle varie funzioni, che poi non verrebbero tradotte in Italiano.
Ma magari tu hai una soluzione anche per questo caso :-)
Io intanto ci studio un po' su perché il foglio dovrebbe essere utilizzato anche da Excel 2003 e la funzione IFERROR (SE.ERRORE in italiano) non è disponibile.
Norman Jones
Jan 25, 2016, 1:04:16 PM1/25/16
to
Ciao Casanamer,
Corraggio una soluzione c'è sempre!
Facendo cura di eseguite la macro dal foglio, anzichè dal VBE, prova il
seguente approccio che funziona a me:
'--------->>
Public Sub Demo2()
Dim WB As Workbook
Dim SH As Worksheet
Dim rngFormule As Range, rCell As Range
Dim s1 As String
Set WB = ThisWorkbook
Set SH = WB.Sheets("Inserimento_Dati")
Set rCell = ActiveCell
With SH
Set rngFormule = SH.Range("J5:J54")
End With
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With rngFormule
.Select
.Formula = s1
DoEvents
Application.SendKeys "{F2}^+~"
End With
XIT:
rCell.Select
With Application
.EnableEvents = True
.ScreenUpdating = True
Regards,
Norman
Facendo cura di eseguite la macro dal foglio, anzichè dal VBE, prova il
seguente approccio che funziona a me:
'--------->>
Public Sub Demo2()
Dim WB As Workbook
Dim SH As Worksheet
Dim rngFormule As Range, rCell As Range
Dim s1 As String
Set WB = ThisWorkbook
Set SH = WB.Sheets("Inserimento_Dati")
Set rCell = ActiveCell
With SH
Set rngFormule = SH.Range("J5:J54")
End With
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With rngFormule
.Select
.Formula = s1
DoEvents
Application.SendKeys "{F2}^+~"
End With
XIT:
rCell.Select
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<<=========
===
End Sub
'<<=========
Regards,
Norman
casanmaner
Jan 25, 2016, 2:46:47 PM1/25/16
to
purtroppo la formula non viene tradotta in italiano.
Nelle celle dell'intervallo viene inserito esattamente (questo è un copia/incolla del contenuto della prima cella dell'intervallo):
=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"",MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,"<="&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,"<="&_EC),))),"",INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"",MATCH(_EC,_EC,0))=ROW(_EC)- (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,"<="&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,"<="&_EC),)))
e, di conseguenza, non imposta nemmeno la formula matriciale, anche se la seguenza richiamata tramite sendkeys è Ctrl+Maiusc+Invio (preceduto dall'F2), così come accade se si cerca di seguire la stessa sequenza direttamente dalla tastiera.
Tu per caso utilizzi una versione in inglese di Excel?
casanmaner
Jan 25, 2016, 2:49:24 PM1/25/16
to
Ah ... aggiungo di non "impazzirci", a meno di una volontà personale per trovare una soluzione :-), perché alla fin fine per semplificarmi le cose ho impostato l'elenco tramite il filtro avanzato attivato quando viene inserito il nome del cliente nelle celle della colonna dedicata.
Norman Jones
Jan 25, 2016, 3:07:39 PM1/25/16
to
>
> Ciao Norman,
> purtroppo la formula non viene tradotta in italiano.
> Nelle celle dell'intervallo viene inserito esattamente (questo è un copia/incolla
> del contenuto della prima cella dell'intervallo):
> =IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"",
MATCH(_EC,_EC,0))=ROW(_EC)-(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,"<="&_EC)
,ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC))
,"",1/COUNTIF(_EC,_EC)))),COUNTIF(_EC,"<="&_EC),))),"",INDEX(_EC,MATCH(SMALL
(IF(IF(ISERROR(MATCH(_EC,_EC,0)),"",MATCH(_EC,_EC,0))=ROW(_EC)- (ROW(_F1)
+1-ROW(R1C2)),1)*COUNTIF(_EC,"<="&_EC),ROW(_EC)-(ROW(_F1)+1-ROW(R1C2))+
ROWS(_EC)-SUM(IF(ISERROR(1/COUNTIF(_EC,_EC)),"",1/COUNTIF(_EC,_EC)))),
COUNTIF(_EC,"<="&_EC),)))
>
> e, di conseguenza, non imposta nemmeno la formula matriciale, anche se la
> seguenza richiamata tramite sendkeys è Ctrl+Maiusc+Invio (preceduto dall'F2),
> così come accade se si cerca di seguire la stessa sequenza direttamente dalla tastiera.
>
> Tu per caso utilizzi una versione in inglese di Excel?
Come hai indovinato, utilizzo una versione inglese di Excel. Di
>
> e, di conseguenza, non imposta nemmeno la formula matriciale, anche se la
> seguenza richiamata tramite sendkeys è Ctrl+Maiusc+Invio (preceduto dall'F2),
> così come accade se si cerca di seguire la stessa sequenza direttamente dalla tastiera.
>
> Tu per caso utilizzi una versione in inglese di Excel?
consguenza non posso provare la formula italiana in situ.
Prova a sostituire
> With rngFormule
> .Select
> .Formula = s1
> DoEvents
> Application.SendKeys "{F2}^+~"
> End With
With rngFormule
.Select
.FormulaLocal = s1
DoEvents
Application.SendKeys "{F2}^+~"
End With
Inoltre sarebbe utile se tu potessi darmi la formula (2003) nella sua
Application.SendKeys "{F2}^+~"
End With
forma italiana. Posso tradurre la formula ma non posso poi verificare la
traduzione della tua lunga formula!
===
Regards,
Norman
casanmaner
Jan 25, 2016, 3:52:25 PM1/25/16
to
Norman devi perdornarmi :-)
A seguito delle modifica che dicevo rispetto all'utilizzo del filtro avanzato si è impostato in quella colonna il formato testo (che è presente nelle celle della colonna originaria) e quindi la formula non veniva tradotta.
Impostando le celle nel formato generale avviene la traduzione.
Però rimane il problema che solo la prima cella dell'intervallo presenta l'inserimento in forma matriciale. Le celle successive risultano inserite come formule non matriciali.
Comunque il testo della formula in italiano è la seguente (anche se a questo punto penso non sia rilevante):
=SE(VAL.ERRORE(INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)));"";INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)- (RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)))
A seguito delle modifica che dicevo rispetto all'utilizzo del filtro avanzato si è impostato in quella colonna il formato testo (che è presente nelle celle della colonna originaria) e quindi la formula non veniva tradotta.
Impostando le celle nel formato generale avviene la traduzione.
Però rimane il problema che solo la prima cella dell'intervallo presenta l'inserimento in forma matriciale. Le celle successive risultano inserite come formule non matriciali.
Comunque il testo della formula in italiano è la seguente (anche se a questo punto penso non sia rilevante):
=SE(VAL.ERRORE(INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)));"";INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)- (RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)))
casanmaner
Jan 25, 2016, 3:56:48 PM1/25/16
to
Però funziona, applicando la matriciale a tutto l'intervallo, se divido in due il sendkey
Application.SendKeys "{F2}"
Application.SendKeys "^+~"
Application.SendKeys "{F2}"
Application.SendKeys "^+~"
casanmaner
Jan 25, 2016, 4:04:01 PM1/25/16
to
Non è tanto l'aver suddiviso l'esecuzione del comnado sendkeys ma l'aver annullato il comando che seleziona la cella che risultava attiva prima di iniziare l'inserimento della formula.
Per intenderci 'rCell.Select
Me ne sono reso conto perché avevo selezionato una delle celle intermedie dell'intervallo dove vengono inserite le formule matriciali e solo quella cella selezionata aveva l'inserimento in matriciale.
casanmaner
Jan 25, 2016, 4:10:07 PM1/25/16
to
Ho spostato il comando sendkeys prima di inserire la formuala dando impostando su true la proprietà wait.
In pratica così
With rngFormule
.Select
Application.SendKeys "{F2}^+~", True
.Formula = s1
DoEvents
End With
Norman Jones
Jan 25, 2016, 4:16:07 PM1/25/16
to
Ciao Casanmener,
> Norman devi perdornarmi
> A seguito delle modifica che dicevo rispetto all'utilizzo del filtro
> avanzato si è impostato in quella colonna il formato testo (che è
> presente nelle celle della colonna originaria) e quindi la formula non
> veniva tradotta.
> Impostando le celle nel formato generale avviene la traduzione.
Meno male! Non ero in grado di capire il compotamenrto della tua versione
> avanzato si è impostato in quella colonna il formato testo (che è
> presente nelle celle della colonna originaria) e quindi la formula non
> veniva tradotta.
> Impostando le celle nel formato generale avviene la traduzione.
> Però rimane il problema che solo la prima cella dell'intervallo
> presenta l'inserimento in forma matriciale. Le celle successive
> risultano inserite come formule non matriciali.
codice:
'=========>>
Option Explicit
'--------->>
Public Sub Demo2()
Dim WB As Workbook
Dim SH As Worksheet
Dim s1 As String
Set WB = ThisWorkbook
Set SH = WB.Sheets("Inserimento_Dati")
Set WB = ThisWorkbook
Set SH = WB.Sheets("Inserimento_Dati")
With SH
Set rngFormule = SH.Range("J5:J54")
End With
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set rngFormule = SH.Range("J5:J54")
End With
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With rngFormule
.Select
.Formula = s1
DoEvents
Application.SendKeys "{F2}+^~"
.Select
.Formula = s1
DoEvents
End With
XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<<=========
Potresti scaricare il mio file di prova Casenmaner#3_20160125.xlsm a:
.ScreenUpdating = True
End With
End Sub
'<<=========
http://1drv.ms/1PgDc9E
===
Regards,
Norman
Norman Jones
Jan 25, 2016, 4:19:47 PM1/25/16
to
Ciao Casenmaner,
> Trovata la soluzione.
> Ho spostato il comando sendkeys prima di inserire la formuala
> dando impostando su true la proprietà wait.
> In pratica così
>
> With rngFormule
> .Select
> Application.SendKeys "{F2}^+~", True
> .Formula = s1
> DoEvents
> End With
>
Vedi il codice suggerito nella mia risposta precedente.
--
===
Regards,
Norman
> Trovata la soluzione.
> Ho spostato il comando sendkeys prima di inserire la formuala
> dando impostando su true la proprietà wait.
> In pratica così
>
> With rngFormule
> .Select
> Application.SendKeys "{F2}^+~", True
> .Formula = s1
> DoEvents
> End With
>
--
===
Regards,
Norman
casanmaner
Jan 25, 2016, 4:34:09 PM1/25/16
to
Però anche mantenendo questa opzione, che è comoda perché altrimenti rimane la selezione dell'intero intervallo, lanciando il comando SendKeys impostando Wait su true le formule vengono inserite in tutto l'intervallo in forma matriciale.
Quindi la versione finale di una procedura per l'inserimento di una formula matriciale la cui stringa risulti superiore a 255 caratteri potrebbe essere questa:
'======================>
Option Explicit
Public Sub InsertFormulaArray()
'Procedura che consente di inserire in una cella, o intervallo di celle, una formula matriciale la cui
'lunghezza supera i 255 caratteri.
'L'inserimento diretto della stringa di testo senza la suddivisione in "sub stringhe" darebbe errore
'Per effettuare l'inserimento occorre sfruttare il comando SendKeys impostando la propietà Wait su True e
'dando il comando prima dell'inserimento nel testo della formula in modalità semplice ".Formula"
'Occore anche impostare DoEvents per lasciare il controllo degli eventi all'applicazione
Dim WB As Workbook
Dim SH As Worksheet
Dim rngFormule As Range, rCell As Range
Dim s1 As String
Set WB = ThisWorkbook
Set SH = WB.Sheets("Inserimento_Dati")
Set rCell = ActiveCell
With SH
End With
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
'corrispondente della formula in Italiano
s1 = "=IF(ISERROR(INDEX(_EC,MATCH(SMALL(IF(IF(ISERROR" _
& "(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& "(ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW" _
& "(_EC)-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR" _
& "(1/COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),))),"""",INDEX(_EC,MATCH(SMALL(IF(IF" _
& "(ISERROR(MATCH(_EC,_EC,0)),"""",MATCH(_EC,_EC,0))=ROW(_EC)-" _
& " (ROW(_F1)+1-ROW(R1C2)),1)*COUNTIF(_EC,""<=""&_EC),ROW(_EC)" _
& "-(ROW(_F1)+1-ROW(R1C2))+ROWS(_EC)-SUM(IF(ISERROR(1/" _
& "COUNTIF(_EC,_EC)),"""",1/COUNTIF(_EC,_EC)))),COUNTIF" _
& "(_EC,""<=""&_EC),)))"
'SE(VAL.ERRORE(INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)));"";INDICE(_EC;CONFRONTA(PICCOLO(SE(SE(VAL.ERRORE(CONFRONTA(_EC;_EC;0));"";CONFRONTA(_EC;_EC;0))=RIF.RIGA(_EC)- (RIF.RIGA(_F1)+1-RIF.RIGA($B$1));1)*CONTA.SE(_EC;"<="&_EC);RIF.RIGA(_EC)-(RIF.RIGA(_F1)+1-RIF.RIGA($B$1))+RIGHE(_EC)-SOMMA(SE(VAL.ERRORE(1/CONTA.SE(_EC;_EC));"";1/CONTA.SE(_EC;_EC))));CONTA.SE(_EC;"<="&_EC);)))
Debug.Print Len(s1)
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With rngFormule
.Select
Application.SendKeys "{F2}^+~", True
.Formula = s1
DoEvents
End With
.Select
Application.SendKeys "{F2}^+~", True
.Formula = s1
DoEvents
End With
XIT:
rCell.Select
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<======================
rCell.Select
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Complimenti Norman :-)
Norman Jones
Jan 25, 2016, 4:50:57 PM1/25/16
to
Ciao Casenmaner,
Ti ringrazio per il cortese riscontro e sono lieto che tu hai risolto il
problema.
Tuttavia, per concludere, suggeritei un codidice finale del genere:
If Application.Version < 12 Then
Call Demo2
Else
call Demo
End If
Complimenti anche a te per tua perseveranza e dedizione eccezionali.
===
Regards,
Norman
Ti ringrazio per il cortese riscontro e sono lieto che tu hai risolto il
problema.
Tuttavia, per concludere, suggeritei un codidice finale del genere:
If Application.Version < 12 Then
Call Demo2
Else
call Demo
End If
Complimenti anche a te per tua perseveranza e dedizione eccezionali.
===
Regards,
Norman
Norman Jones
Jan 25, 2016, 4:52:25 PM1/25/16
to
On 25/01/2016 21:50, Norman Jones wrote:
> Tuttavia, per concludere, suggeritei un codidice finale del genere:
Tuttavia, per concludere, suggerirei un codice finale del genere:-
> Tuttavia, per concludere, suggeritei un codidice finale del genere:
===
Regards,
Norman
casanmaner
Jan 25, 2016, 4:59:53 PM1/25/16
to
Il giorno lunedì 25 gennaio 2016 22:52:25 UTC+1, Norman Jones ha scritto:
> On 25/01/2016 21:50, Norman Jones wrote:
> > Tuttavia, per concludere, suggeritei un codidice finale del genere:
>
> Tuttavia, per concludere, suggerirei un codice finale del genere:-
>
>
Si era capito ... sapessi esprimermi io in inglese un decimo di quanto fai tu in italiano :D
> On 25/01/2016 21:50, Norman Jones wrote:
> > Tuttavia, per concludere, suggeritei un codidice finale del genere:
>
> Tuttavia, per concludere, suggerirei un codice finale del genere:-
>
>
Grazie ancora
0 new messages