Ensemble ArrayFormula à de nombreuses cellules Excel à l'aide de VBA
J'ai une formule de tableau qui renvoie une seule valeur, et je veux donner tout un tas de cellules de cette même formule de tableau. Le problème est quand je attribuer le tableau de la formule de la gamme, il interprète la formule de telle manière que tous les autres le partage de la sortie d'un seul appel à la formule de tableau, plutôt que de chacun d'entre eux de la sortie d'une valeur distincte.
Pour vous montrer ce que je veux dire, je suis en utilisant le code suivant:
With MarginalData
.Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count)).FormulaArray = pullFormula
End With
Ce que je veux, est un résultat qui ressemble à ceci:
C'est à quoi il ressemble quand je rentre la formule matricielle séparément dans chaque cellule de la plage.
Mais ce que je reçois est ceci:
La sortie de la formule de tableau dans la première cellule est répété dans toutes les colonnes - ils partagent tous la même sortie.
Comment puis-je en programmant attribuer la formule de tableau, comme si chaque cellule avait attribué séparément?
La formule est:
{=INDEX(BatchResults,MATCH(TTID&CHAR(1)&ROW()-1,BatchResultsTTIDS&CHAR(1)&BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))}
Il faut le mettre dans une formule de tableau, car il effectue un match non pas sur une seule colonne, mais sur les deux concaténées colonnes. La concaténation des colonnes doivent être retournées dans un tableau, d'où la formule doit être saisi comme une formule de matrice.
La solution la plus simple jusqu'à présent, une variante de la accepté de répondre ci-dessous, est la suivante:
Const pullFormula = "=INDEX(BatchResults,MATCH(TTID&CHAR(1)&ROW()-1,BatchResultsTTIDS&CHAR(1)&BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))"
With wrksht
With .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
.Formula = pullFormula
.FormulaArray = .FormulaR1C1
End With
End With
Comme vous pouvez le voir ci-dessus, pullFormula dépend déjà de la cellule. Il prend en compte la ligne actuelle, et l'en-tête de colonne (
A$1
), qui est différent pour chaque colonne.Bonne question. Et comme par cet article vous avez la meilleure approche
OriginalL'auteur Alain | 2011-05-18
Vous devez vous connecter pour publier un commentaire.
Ou reprendre la Formule de Tableau comme L1C1, céder à la plage comme FormulaR1C1, puis d'affecter la FormulaR1C1 en tant que Formule matricielle. Cela suppose de la Formule de Tableau dans la cellule A2
Ouch c'est assez lent sur de grandes plages de! Il serait peut-être plus rapide d'utiliser ma méthode pour la première ligne, puis le remplissage automatique vers le bas par exemple Rng.AutoFill Destination:=Rng.Redimensionner(999, Rng.Les colonnes.Le comte), Type:=xlFillDefault. Ou à l'aide de
Application.Calculation = xlCalculationManual
est un peu plus rapideOriginalL'auteur osknows
Au lieu de$1, essayez de
Ce n'est pas tout ce qui ne va pas avec elle, pour que cette fonction fonctionne après que vous avez converti la référence dans une chaîne, on est obligé d'utiliser l'INDIRECTS de la fonction, qui est volatile et les moyens de ces coûteux formules seront recalculées à chaque fois que quelque chose est changé dans le classeur, ce qui est horriblement inacceptable.
Et même si quelqu'un mis en place avec l'INDIRECTS formule, votre changement ENCORE ne mène à rien, l'ensemble de la gamme est toujours considérée comme ayant une seule sortie, et même de répéter problème se produit plutôt que chaque cellule obtenir sa propre valeur.
Encore une fois, aucune idée de pourquoi c'est tout upvotes.
Je suis moins déçu de votre réponse et de plus en plus déçu par le fait que 3 personnes bêtement pris en charge une réponse qui, manifestement, n'était pas correct. Nous faisons tous des erreurs, mais les gens ne devraient pas voter, s'ils ne sont pas de vérifier les réponses.
OriginalL'auteur Lance Roberts
Essayer de le faire de manière semi-automatique. Formule pour la première ligne, puis utiliser FillDown.
Oui, certainement une solution de contournement. Je n'ai pas vraiment utilisé FormulaR1C1, mais je me souviens que maintenant.
OriginalL'auteur Steve Mallory