Définir une plage pour avoir une macro exécutée sur toutes les lignes peuplées dans une feuille de calcul

J'ai réécrit votre code avec des explications qui, je l'espère, suffisent pour comprendre pourquoi. Il y a beaucoup plus que je pourrais dire. J'espère que c'est un bon équilibre entre trop peu et trop.

Cependant, je dois souligner qu'il existe d'excellents outils de gestion de projet disponibles. Je ne crois pas que ce soit un bon usage de votre time.

Points randoms

Sur les ordinateurs 32 bits, Long est mieux que Integer.

Ne pas déclarer vos variables dans une boucle. La scope d'une variable déclarée dans une sous-routine est la sous-routine afin de les déclarer en haut de la sous-routine.

Vous pouvez déclarer toutes vos variables dans une seule instruction Dim , mais je trouve cela confus à less qu'il n'y ait une association réelle entre deux variables ou plus. Je pourrais avoir:

 Dim RodRate As Long, GarthRate As Long, DerekRate As Long 

parce que ces variables sont associées. Cependant, le problème avec cette approche est que vous devrez append MaryRate et JohnRate et AngelaRate lorsque ces personnes rejoindront votre projet.

Vous avez besoin d'un tableau:

 Dim PersonRate(1 To 3) As Long 

où PersonRate(1) = taux pour Rod, PersonRate(2) = taux pour Garth et PersonRate(3) = taux pour Derek.

Mais ce n'est guère mieux. Vous voulez une table qui peut grandir. Donc aujourd'hui:

  Name Rate Rod 20 Garth 25 Derek 15 

Prochaine semaine:

  Name Rate Rod 20 Garth 25 Derek 15 Mary 30 

Avec cela, vous prenez le nom du Cessionnaire, exécutez la table jusqu'à ce que vous trouviez leur nom, puis regardez votre taux.

Je suppose que vous avez une table comme celle-ci dans Sheet2. Vous pouvez continuer à revenir à Sheet2 mais mieux pour charger la table dans un tableau.

Nous pourrions avoir:

 Dim PersonName() As Ssortingng Dim PersonRate() As Long 

Donc PersonRate(2) donne le taux pour PersonName(2) .

Notez dans ma première déclaration de tableau que j'ai écrit: PersonRate(1 To 3) . Cette fois, les parenthèses sont vides. Avec PersonRate(1 To 3) , je dis que je veux exactement trois inputs dans le tableau et que cela ne peut pas être modifié. Avec PersonRate() , je dis que je veux un tableau, mais je ne saurai pas combien d'inputs jusqu'à ce que le time d'exécution.

J'ai dit que nous pourrions avoir deux arrays, PersonName() et PersonRate() et c'est ce que j'ai fait. C'est une approche facile à comprendre, mais je ne pense pas que ce soit la meilleure approche. Je préfère les structures. Lorsque vous avez travaillé cette macro et avant de commencer votre prochaine search User Types qui est le nom VBA d'une structure.

Considérer:

  With Sheets("Sheet2") RowMax = .Cells(Rows.Count, "A").End(xlUp).Row End With 

Il y a beaucoup à expliquer ici.

Cells signifie que je souhaite aborder une cellule dans le classur actif. .Cells signifie que je souhaite aborder une cellule dans la feuille identifiée dans l'instruction With. Cela signifie que je n'ai pas besoin de sélectionner Sheet1 ou Sheet2 pour regarder leur contenu. La sélection des feuilles de travail est lente et le code tend à être plus difficile à comprendre.

.Cells(Row, Column) identifie une cellule. La ligne doit être un nombre, mais la colonne peut être un nombre ou un code de colonne: A = 1, B = 2, Z = 26, AA = 27, etc.

Rows.Count renvoie le nombre de lignes dans une feuille pour la version d'Excel que vous utilisez. Donc .Cells(Rows.Count, "A") identifie le bas de la colonne "A".

End(xlUp) est l'équivalent VBA de cliquer sur Ctrl + UpArrow . Si vous n'êtes pas familier avec Ctrl + Arrow je vous suggère de jouer avec ces quatre controls. Notez que ces controls donnent des résultats faciles à comprendre avec une table rectangular. Cependant, s'il existe des cellules vides, les résultats peuvent être étranges.

Mettre set: .Cells(Rows.Count, "A").End(xlUp).Row faut commencer au bas de la colonne A, monter jusqu'à ce que vous frappiez une cellule avec une valeur et renvoyez son numéro de ligne. Donc, RowMax définit la dernière ligne de la table Rate. Lorsque vous ajoutez la ligne 5 avec le nom et le tarif de Mary, ce code sera automatiquement réglé.

Code révisé

Cela devrait être suffisant pour vous aider à démarrer. Bienvenue dans les joies de la programmation.

 ' * Require all variables to be declared which means a misspelt name ' is not taken as an implicit declaration Option Explicit Sub GetCost() Dim Estimate As Integer Dim Assignee As Ssortingng Dim TotalCost As Integer Dim PersonName() As Ssortingng Dim PersonRate() As Ssortingng Dim InxPerson As Long Dim RowCrnt As Long Dim RowMax As Long ' You can declare constants and use them in place of literals. ' You will see why later. I could have made these ssortingngs and ' used "A", "B", "D", "E" and "F" as the values. Change if that ' is easier for you. Const ColS2Name As Long = 1 Const ColS2Rate As Long = 2 Const ColS1Estimate As Long = 4 Const ColS1Assignee As Long = 5 Const ColS1Total As Long = 6 ' Before doing anything else we must load PersonName and PersonRate from ' Sheet2. I assume the structure of Sheet2 is: ' AB ' 1 Name Rate ' 2 Rod 20 ' 3 Garth 25 ' 4 Derek 15 With Sheets("Sheet2") RowMax = .Cells(Rows.Count, ColS2Name).End(xlUp).Row ' I now know how big I want the the name and rate arrays to be ReDim PersonName(1 To RowMax - 1) ReDim PersonRate(1 To RowMax - 1) ' Load these arrays For RowCrnt = 2 To RowMax ' I could have used 1 and 2 or "A" and "B" for the column ' but this is easier to understand particularly if you come ' back to this macro in six month's time. PersonName(RowCrnt - 1) = .Cells(RowCrnt, ColS2Name).Value PersonRate(RowCrnt - 1) = .Cells(RowCrnt, ColS2Rate).Value Next End With With Sheets("Sheet1") ' I am using the same variable for rows in sheets Sheet1 and Sheet2. ' This is OK because I never look at Sheet1 and Sheet2 at the same time. RowCrnt = 2 Do Until IsEmpty(.Cells(RowCrnt, ColS1Estimate)) Estimate = .Cells(RowCrnt, ColS1Estimate).Value Assignee = .Cells(RowCrnt, ColS1Assignee).Value .Cells(RowCrnt, ColS1Total).Value = 0 ' Locate the Assignee in the PersonName array and ' extract the matching rate For InxPerson = 1 To UBound(PersonName) If PersonName(InxPerson) = Assignee Then .Cells(RowCrnt, ColS1Total).Value = Estimate * PersonRate(InxPerson) Exit For End If Next RowCrnt = RowCrnt + 1 Loop End With End Sub 

La réponse de Tony est une excellente solution et une introduction à la programmation et très bien écrite, donc j'ai +1. Cependant, sauf si je manque quelque chose, le code devrait toujours être le dernier recours dans Excel car il est très lent par rapport aux formules, j'aurais pensé qu'une simple search suffirait, quelque chose comme:

 =D2*(vlookup(E2,'sheet2'!A:B,2,FALSE)) 

Copié dans la colonne