Macro multiplication avec Variable

Le 10/03/2016 à 16:49

Bonjour à tous,

Je travaille actuellement sur une partie de code qui doit me permettre d'automatiser des multiplications.

La petite particularité est que je veux que ce code ne se réfère qu'uniquement à des variables définit en amont de la multiplication, ce qui pourrait me permettre une plus grande liberté dans la modification des feuilles Excel.

C'est pour cela que je ne fige aucune de mes colonnes, ni les colonnes devises, ni les colonnes dans l'onglet données.

Un autre de mes problèmes est que je veux que quand je lance la macro, elle ne s'exécute QUE sur la dernière ligne vide, et non pas qu'elle recalcule toute la colonne, or j'ai du mal à parvenir à ce résultat.

J'ai tenté de définir une variable "NextRow" mais sans succès...

Option Explicit

'Remplir la colonne Amount in Euros automatiquement
Sub Column_Amount_in_euros()

    Dim Column_Amount_in_euros As Variant
    Dim Column_Amount_billed_devise As Variant
    Dim Column_Currency As Variant
    Dim nb_c As Integer
    Dim nb_l As Integer
    Dim nb_j As Integer
    Dim nb_i As Integer
    Dim Devise_Value_USD As Integer
    Dim Devise_Value_GBP As Integer
    Dim Devise_Value_THB As Integer
    Dim i As Integer
    Dim j As Integer
    Dim y As Integer
    Dim NextRow As Range

    nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_l = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies
    nb_j = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_i = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies

    Sheets("Cours devise").Activate

    For i = 1 To nb_i
    For j = 1 To nb_j
    If Cells(i, j) Like "*USD*" Then Devise_Value_USD = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*GBP*" Then Devise_Value_GBP = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*THB*" Then Devise_Value_THB = Cells(i, j).Offset(0, 1).Value

    Next j
    Next i

    Sheets("Données").Activate

    For i = 1 To nb_c
    If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column
    If Cells(1, i) Like "*Amount billed devise*" Then Column_Amount_billed_devise = Cells(1, i).Column
    If Cells(1, i) Like "*Currency*" Then Column_Currency = Cells(1, i).Column
    Next i

'
'    'Détermine la prochaine ligne vide
    Set NextRow = Range("*Column_Amount_in_euros*" & Rows.Count).End(xlUp)
    NextRow.Select

    'Multiplication selon la devise par le bon taux de change

    For y = 2 To nb_l

    Select Case Cells(NextRow, Column_Currency).Value

    Case "USD"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_USD

    Case "GBP"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_GBP

    Case "THB"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_THB

    Case "EUR"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise).Value

    Case Else
        Cells(NextRow, Column_Amount_in_euros) = "To determined"

    End Select

    Next y

End Sub

Mon code n'est pas du tout parfait et je pense que c'est du au fait que je définis mal et/ou utilise mal mes variables. Ayez un peu d'indulgence, je débute tout juste en VBA...

Le code se trouve dans le module 1.

Est ce possible de me donner un coup de main sur le sujet ?

Merci d'avance à qui voudra bien y jeter un coup d'œil

Bonne fin de journée,

Jeanne

32formulaire-billing-version-tva.xlsm (21.11 Ko)
Le 10/03/2016 à 17:35

'Détermine la prochaine ligne vide

Set NextRow = Range("*Column_Amount_in_euros*" & Rows.Count).End(xlUp)

NextRow.Select

Sheet("feuilleconcerné").cells(row.count,"A").end(xlup).row+1

code determinant la derniere ligne pleine,+1 donne 1ere ligne vide(sur la colonne A)

Le 10/03/2016 à 17:41

Merci mais dans ce cas, ça va prendre la première ligne vide en se référant à la colonne A non ?

Or je voudrais la première ligne vide en se référant à la colonne que je veux remplir...

C'est à dire la colonne variable "Colonne_Amount_In_Euros"

Le 10/03/2016 à 18:01

tente un melange entre les 2 codes:

 NextRow =Sheet("feuilleconcerné"). Range("*Column_Amount_in_euros*" & Rows.Count).End(xlUp).row+1
Le 10/03/2016 à 18:06

j'ai tenté mais il refuse la ligne de code quand j'appuie sur F5

Le 10/03/2016 à 18:11

Héhé, si cette partie fonctionne:

If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column

elle retourne une valeur numerique donc

NextRow =Sheet("feuilleconcerné"). Cells( Rows.Count,Column_Amount_in_euros).End(xlUp).row+1
Le 10/03/2016 à 18:15

La partie précédente fonctionne, elle renvoie bien 11 ce qui est le numéro de ma colonne mais, aller savoir pourquoi, la ligne que vous m'avez fournie ne passe pas le Débogage quand je presse F5 

NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

Merci pour votre aide en tout cas

C'est à n'y rien comprendre...

Le 10/03/2016 à 18:34
Dim NextRow As Range

c'est plus un range 

Set NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

et il acceptera pas le Set non plus

de+, tu cherche la 1ere ligne vide pour l'entré dans tes calculs....

Le 11/03/2016 à 10:36

Effectivement, il ne reconnait pas le Set...

As tu une idée pour corriger cela ?

Avec tes corrections, mon code complet devient :

Option Explicit

'Remplir la colonne Amount in Euros automatiquement
Sub Column_Amount_in_euros()

    Dim Column_Amount_in_euros As Variant
    Dim Column_Amount_billed_devise As Variant
    Dim Column_Currency As Variant
    Dim nb_c As Integer
    Dim nb_l As Integer
    Dim nb_j As Integer
    Dim nb_i As Integer
    Dim Devise_Value_USD As Integer
    Dim Devise_Value_GBP As Integer
    Dim Devise_Value_THB As Integer
    Dim i As Integer
    Dim j As Integer
    Dim y As Integer
    Dim NextRow As Variant

    nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_l = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies
    nb_j = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_i = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies

    Sheets("Cours devise").Activate

    For i = 1 To nb_i
    For j = 1 To nb_j
    If Cells(i, j) Like "*USD*" Then Devise_Value_USD = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*GBP*" Then Devise_Value_GBP = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*THB*" Then Devise_Value_THB = Cells(i, j).Offset(0, 1).Value

    Next j
    Next i

    Sheets("Données").Activate

    For i = 1 To nb_c
    If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column
    If Cells(1, i) Like "*Amount billed devise*" Then Column_Amount_billed_devise = Cells(1, i).Column
    If Cells(1, i) Like "*Currency*" Then Column_Currency = Cells(1, i).Column
    Next i

'
'    'Détermine la prochaine ligne vide
    Set NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

    'Multiplication selon la devise par le bon taux de change

    For y = 2 To nb_l

    Select Case Cells(NextRow, Column_Currency).Value

    Case "USD"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_USD

    Case "GBP"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_GBP

    Case "THB"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_THB

    Case "EUR"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise).Value

    Case Else
        Cells(NextRow, Column_Amount_in_euros) = "To determined"

    End Select

    Next y

End Sub

Je me dis qu'une possible erreur vient peut etre aussi de la définition de mes colonnes contenant la valeur de la devise... ?

Qu'en penses tu ?

Le 11/03/2016 à 11:14
Option Explicit

    'Remplir la colonne Amount in Euros automatiquement
    Sub Column_Amount_in_euros()

        Dim Column_Amount_in_euros As Integer
        Dim Column_Amount_billed_devise As Integer
        Dim Column_Currency AsInteger
        Dim nb_c As Integer
        Dim nb_l As Integer
        Dim nb_j As Integer
        Dim nb_i As Integer
        Dim Devise_Value_USD As Integer
        Dim Devise_Value_GBP As Integer
        Dim Devise_Value_THB As Integer
        Dim i As Integer
        Dim j As Integer
        Dim y As Integer
        Dim NextRow As Integer

        nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
       nb_l = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies
       nb_j = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
       nb_i = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies

        Sheets("Cours devise").Activate

        For i = 1 To nb_i
        For j = 1 To nb_j
        If Cells(i, j) Like "*USD*" Then Devise_Value_USD = Cells(i, j).Offset(0, 1).Value
        If Cells(i, j) Like "*GBP*" Then Devise_Value_GBP = Cells(i, j).Offset(0, 1).Value
        If Cells(i, j) Like "*THB*" Then Devise_Value_THB = Cells(i, j).Offset(0, 1).Value

        Next j
        Next i

        Sheets("Données").Activate

        For i = 1 To nb_c
        If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column
        If Cells(1, i) Like "*Amount billed devise*" Then Column_Amount_billed_devise = Cells(1, i).Column
        If Cells(1, i) Like "*Currency*" Then Column_Currency = Cells(1, i).Column
        Next i

    '
    '    'Détermine la prochaine ligne vide
NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

        'Multiplication selon la devise par le bon taux de change

        For y = 2 To nb_l

        Select Case Cells(NextRow, Column_Currency).Value 'next row correspond a une ligne vide
         Select Case Cells(y, Column_Currency).Value 'plutot

        Case "USD"
Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_USD
'next row correspond a une ligne vide du coup, 
        Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_USD
' cellule lignevide=calcul cellule en cours(boucle)
        Case "GBP"
       Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_GBP
Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_GBP
        Case "THB"
       Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_THB
 Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_THB
        Case "EUR"
       Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise).Value
 Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise).Value
        Case Else
            Cells(NextRow, Column_Amount_in_euros) = "To determined"

        End Select

        Next y

    End Sub

Donc j'ai surligner les choses a modifier, dis moi si le raisonnement est bon et si ca fonctionne

Le 11/03/2016 à 11:25

Miracle !! Le nextRow fonctionne parfaitement avec ce bout de code c'est à dire que j'ai bien de NextRow de ma colonne à chaque fois, merci

Par contre il ne reconnait plus la devise... c'est à dire qu'il ne multiplie par rien, c'est comme si la devise n'avait pas de valeur

Mais c'est quand meme bizarre parce qu'il fait parfaitement ce que je veux pour le NextRow mais maintenant, il prend uniquement la valeur de la dernier ligne pour la multiplication...

Le code est donc devenu :

Option Explicit

'Remplir la colonne Amount in Euros automatiquement
Sub Column_Amount_in_euros()

    Dim Column_Amount_in_euros As Integer
    Dim Column_Amount_billed_devise As Integer
    Dim Column_Currency As Integer
    Dim nb_c As Integer
    Dim nb_l As Integer
    Dim nb_j As Integer
    Dim nb_i As Integer
    Dim Devise_Value_USD As Integer
    Dim Devise_Value_GBP As Integer
    Dim Devise_Value_THB As Integer
    Dim i As Integer
    Dim j As Integer
    Dim y As Integer
    Dim NextRow As Integer

    nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_l = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies
    nb_j = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_i = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies

    Sheets("Cours devise").Activate

    For i = 1 To nb_i
    For j = 1 To nb_j
    If Cells(i, j) Like "*USD*" Then Devise_Value_USD = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*GBP*" Then Devise_Value_GBP = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*THB*" Then Devise_Value_THB = Cells(i, j).Offset(0, 1).Value

    Next j
    Next i

    Sheets("Données").Activate

    For i = 1 To nb_c
    If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column
    If Cells(1, i) Like "*Amount billed devise*" Then Column_Amount_billed_devise = Cells(1, i).Column
    If Cells(1, i) Like "*Currency*" Then Column_Currency = Cells(1, i).Column
    Next i

    'Détermine la prochaine ligne vide
    NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

    'Multiplication selon la devise par le bon taux de change

    For y = 2 To nb_l

    Select Case Cells(y, Column_Currency).Value

    Case "USD"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_USD

    Case "GBP"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_GBP

    Case "THB"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_THB

    Case "EUR"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise).Value

    Case Else
        Cells(NextRow, Column_Amount_in_euros) = "To determined"

    End Select

    Next y

End Sub
valeur derniere ligne
Le 11/03/2016 à 12:06
Dim Devise_Value_USD As Single
    Dim Devise_Value_GBP As Single
    Dim Devise_Value_THB As Single

encore un soucis de declarations, j'avais pas fait attention...ce sont des decimales, pas des entiers

Pour information sur les declarations:

https://www.excel-pratique.com/fr/vba/variables.php

Mais c'est quand meme bizarre parce qu'il fait parfaitement ce que je veux pour le NextRow mais maintenant, il prend uniquement la valeur de la dernier ligne pour la multiplication...

j'ai pas du tout verifié ce que faisait les calculs...

Cells(NextRow, Column_Amount_in_euros).Value = Cells(y, Column_Amount_billed_devise) * Devise_Value_USD

correspond a:

Valeur cellule(premierelignevide, colonne Amount_in_euros)=cellule(ligne en cours,colonne Amount_billed_devise)*Devise_Value_USD

si c'est le but, le probleme de declarations devrait tt regler

Le 11/03/2016 à 12:27

Alors en bidouillant un peu, j'ai réussi à avoir exactement le résultat que voulais, mais je n'utilise plus de y, j'ai remplacé tout les y par des "NextRow" et ça marche parfaitement ! Merci beaucoup pour ton aide !

Peux tu me dire si tu trouve le code bien ? (pour pallier aux prochaines erreurs ^^)

Et merci pour le lien sur les variables, je vais aller me documenter un peu du coup !

Option Explicit

'Remplir la colonne Amount in Euros automatiquement
Sub Column_Amount_in_euros()

    Dim Column_Amount_in_euros As Integer
    Dim Column_Amount_billed_devise As Integer
    Dim Column_Currency As Integer
    Dim nb_c As Integer
    Dim nb_l As Integer
    Dim nb_j As Integer
    Dim nb_i As Integer
    Dim Devise_Value_USD As Single
    Dim Devise_Value_GBP As Single
    Dim Devise_Value_THB As Single
    Dim i As Integer
    Dim j As Integer
    Dim y As Integer
    Dim NextRow As Integer

    nb_c = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_l = ThisWorkbook.Sheets("Données").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies
    nb_j = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Column 'Nombre de colonnes remplies
    nb_i = ThisWorkbook.Sheets("Cours devise").UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Nombre de lignes remplies

    Sheets("Cours devise").Activate

    For i = 1 To nb_i
    For j = 1 To nb_j
    If Cells(i, j) Like "*USD*" Then Devise_Value_USD = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*GBP*" Then Devise_Value_GBP = Cells(i, j).Offset(0, 1).Value
    If Cells(i, j) Like "*THB*" Then Devise_Value_THB = Cells(i, j).Offset(0, 1).Value

    Next j
    Next i

    Sheets("Données").Activate

    For i = 1 To nb_c
    If Cells(1, i) Like "*Amount in euros*" Then Column_Amount_in_euros = Cells(1, i).Column
    If Cells(1, i) Like "*Amount billed devise*" Then Column_Amount_billed_devise = Cells(1, i).Column
    If Cells(1, i) Like "*Currency*" Then Column_Currency = Cells(1, i).Column
    Next i

    'Détermine la prochaine ligne vide
    NextRow = Sheets("Données").Cells(Rows.Count, Column_Amount_in_euros).End(xlUp).Row + 1

    'Multiplication selon la devise par le bon taux de change

    Select Case Cells(NextRow, Column_Currency).Value

    Case "USD"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_USD

    Case "GBP"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_GBP

    Case "THB"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise) * Devise_Value_THB

    Case "EUR"
    Cells(NextRow, Column_Amount_in_euros).Value = Cells(NextRow, Column_Amount_billed_devise).Value

    Case Else
    Cells(NextRow, Column_Amount_in_euros) = "To determined"

    End Select

End Sub
Le 11/03/2016 à 12:44

ça marche parfaitement !

Dans ce cas...

Peux tu me dire si tu trouve le code bien ? (pour pallier aux prochaines erreurs ^^)

Si t'a compris le fonctionnement, les erreurs t'apparaitront sans probleme.

si t'a besoin de commentaires sur le code, previens moi

Le 11/03/2016 à 14:05

Merci encore en tout cas

Rechercher des sujets similaires à "macro multiplication variable"