Calculer distance liste de communes depuis tableau distancier
Bonjour le forum !
ça fait quelques heures que je bricole un petit bout de macro et grrrrr j'y arrive paaaaaaaaaaaaas.
J'ai un distancier dans ma feuil1.
Dans ma feuil2, j'ai une liste de communes de départ et d'arrivée. Je voudrai calculer automatiquement la distance entre ces communes d'après mon distancier en feuil1.
Je m’emmêle les pinceaux avec toutes ces boucles.
Voici mon code, ainsi que le fichier :
Sub calculer()
Dim der_ligne, der_colonne, der, ligne, colonne, i As Integer
der_colonne = Feuil1.Cells.SpecialCells(xlCellTypeLastCell).Column
der_ligne = Feuil1.Cells.SpecialCells(xlCellTypeLastCell).Row
der = Feuil2.Cells.SpecialCells(xlCellTypeLastCell).Row
For ligne = 2 To der_ligne
For colonne = 2 To der_colonne
For i = 1 To der
If InStr(Feuil2.Cells(i, 6).Value, Feuil1.Cells(1, colonne).Value) And InStr(Feuil2.Cells(i, 8).Value, Feuil1.Cells(ligne, 1).Value) Then
Feuil2.Cells(i, 9).Value = Feuil1.Cells(ligne, colonne).Value
End If
Next i
Next colonne
Next ligne
End SubQuelqu'un peut-il m'aider ?
Un GRAND merci par avance,
PP
Bonjour,
Une proposition Power Query, listes déroulantes avec les fonctions INDEX() et EQUIV().
Cdlt.
Bonsoir,
Jean-Eric bonsoir,
je n'y connais rien en Power Query alors je me permet de proposer ce fichier :
Il reprend en effet les instructions INDEX et EQUIV.
Pour cela j'ai nommai trois plages :
la première "distancier" c'est le tableau complet,
la deuxième c'est "départ" qui correspond à la colonne d'entête (données de la colonne gauche du tableau)
la troisième c'est "arrivée" qui est la ligne d'entête du tableau.
Du coup pour trouver la distance entre deux villes j'utilise INDEX(ligne,colonne) où ligne et colonne sont remplacés par l'instruction EQUIV de chaque entête.
En effet EQUIV vous revoie la position d'une valeur donnée dans une plage et INDEX renvoi la donnée se trouvant à l'intersection d'une ligne et d'une colonne, on a donc tout pour trouver :
=INDEX(Distancier;EQUIV("Ville de départ";Départ);EQUIV("Ville d'arrivée";Arrivée))
où bien évidemment les guillemets "ville de départ" et "ville d'arrivée" sont à remplacer par les référence des cellules où les données se trouvent.
@ bientôt
LouReeD
Bonsoir tulipe_4 !
D'où vous vient cette idée de somme des distances ?
Sinon belle formule une fois de plus !
@ bientôt
LouReeD
Je vous remercie beaucoup pour vos réponses, je vais regarder tout ça.
En réalité, je voulais absolument éviter les formules et utiliser VBA.
Pour vous expliquer en quelques mots, au bureau nous avons un logiciel qui permet d'extraire une liste de personnes sous format excel, avec comme principales informations commune de départ et d'arrivée.
Nous avons par ailleurs, un autre fichier de suivi. J'avais dans l'idée, d'intégrer le distancier dans ce fichier de suivi et depuis un bouton, aller chercher l'extraction de mes personnes, et de calculer automatiquement la distance qui sépare les communes.
Sachant que nous pouvons faire une dizaine d'extraction par jour, et que celles-ci sont générées automatiquement, la fonction "Instr" que j'avais utilisé me semblait bien : dans l'extraction, il y a parfois des Cedex "Metz Cedex" ou des tirets "Villers-les-Nancy".
Merci tout de même pour votre temps,
Je vais regarder et voir ce que je peux faire,
PP
Bonjour,
La proposition Power Query est à envisager pour créer la relation entre tes 2 fichiers.
Maintenant sans fichiers !?
Cdlt.
Re,
@tulipe_4,
Et une tartine sans Kiri ?
Ne pas oublier le verre de lait !
Cdlt
tu ne mets pas le 0 à Equiv
Mince je pensais que c'était le 0 qui était par défaut, et bien non c'est le 1, car en effet on a le choix entre -1, 0 et 1 (où le 1 ou omis)
Merci tulipe_4 par cette piqure de rappel !
@ bientôt
LouReeD
bonjour
si tu veux utiliser le VBA
test ceci pour voir
j'ai un peu modifier ton code
pour moi ça semble marcher
A plus sur le forum
Bonjour,
Une autre proposition à étudier.
Cdlt.
Public Sub CalculDistances()
Dim rng As Range
Dim lastRow As Long, lRow As Long
Dim vCol, vRow
Set rng = Worksheets("Feuil1").Cells(1).CurrentRegion
With Worksheets("Feuil2")
lastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
For lRow = 1 To lastRow
vCol = Application.Match(.Cells(lRow, 8).Value, rng.Rows(1), 0)
If Not IsError(vCol) Then
vRow = Application.Match(.Cells(lRow, 6).Value, rng.Columns(1), 0)
If Not IsError(vRow) Then .Cells(lRow, 9).Value = Application.Index(rng, vRow, vCol)
End If
Next lRow
End With
End SubJe vous remercie infiniment pour vos réponses et le temps consacré à ma demande.
J’ai réussi à faire ce que je voulais grâce à vous tous.
Bien à vous,
PP