Bonjours a tous !
J'expose mon probleme :
J'ai fait une macro destinee a remplir 20000 lignes d'une feuille a partir de certains criteres.
La macro fonctionne lorsque les criteres sont inscrit directement sous VBA mais ne fonctionne pas quand je me refere a des cellules.
je m'explique avec le code ci-dessous,P= 2 et PBOP1 = 14 par exemple. Si dans mon inequation (de l'option 1) j'ecris 14 au lieu de PBOP1 ca fonctionne. Mais quand j'utilise le PBOP1 (qui renvoi bien 14) il me retourne un"false"...
ca depasse mes competances, je suis loin d'etre un expert ...
Le code :
Merci d'avance pour vos reponses (toute critique est bonne a prendre, n'hesitez pas !)
J'expose mon probleme :
J'ai fait une macro destinee a remplir 20000 lignes d'une feuille a partir de certains criteres.
La macro fonctionne lorsque les criteres sont inscrit directement sous VBA mais ne fonctionne pas quand je me refere a des cellules.
je m'explique avec le code ci-dessous,P= 2 et PBOP1 = 14 par exemple. Si dans mon inequation (de l'option 1) j'ecris 14 au lieu de PBOP1 ca fonctionne. Mais quand j'utilise le PBOP1 (qui renvoi bien 14) il me retourne un"false"...
ca depasse mes competances, je suis loin d'etre un expert ...
Le code :
Code:
Sub Calculation()
'
' Calculation Macro
'
CreateObject("Wscript.shell").Popup "The macro has started, you can see the progression on the bottom of the window" & Chr(10) & Chr(10) & "This message will disappear at the end of the macro", 2, "Information", vbExclamation
Application.ScreenUpdating = False
Sheets("step(2)").Select
Dim LastRow, Counter
LastRow = Range("A65536").End(xlUp).Row
Counter = 2
Sheets("Delivery cost").Select
Dim PlOP1, PBOP1, PlOP2, PBOP2, PlOP3, PBOP3
PlOP1 = Range("B2").Value
PBOP1 = Range("F2").Value 'option 1 prix
PlOP2 = Range("B3").Value
PBOP2 = Range("F3").Value 'option 2 prix
PlOP3 = Range("B4").Value
PBOP3 = Range("F4").Value 'option 3 prix
Dim PlOP4, PBOP4, PlOP5, PBOP5, PlOP6, PBOP6
PlOP4 = Range("B5").Value
PBOP4 = Range("F5").Value 'option 4 prix
PlOP5 = Range("B6").Value
PBOP5 = Range("F6").Value 'option 5 prix
PlOP6 = Range("B7").Value
PBOP6 = Range("F7").Value 'option 6 prix
Dim PlOP7, PBOP7, PlOP8, PBOP8, PlOP9, PBOP9
PlOP7 = Range("B8").Value
PBOP7 = Range("F8").Value 'option 7 prix
PlOP8 = Range("B9").Value
PBOP8 = Range("F9").Value 'option 8 prix
PlOP9 = Range("B10").Value
PBOP9 = Range("F10").Value 'option 9 prix
Dim PlOP10, PBOP10, PlOP11, PBOP11, PlOP12, PBOP12
PlOP10 = Range("B11").Value
PBOP10 = Range("F11").Value 'option 10 prix
PlOP11 = Range("B12").Value
PBOP11 = Range("F12").Value 'option 11 prix
PlOP12 = Range("B13").Value
PBOP12 = Range("F13").Value 'option 12 prix
Dim PlOP13, PBOP13, PlOP14, PBOP14
PlOP13 = Range("B14").Value
PBOP13 = Range("F14").Value 'option 13 prix
PlOP14 = Range("B15").Value
PBOP14 = Range("F15").Value 'option 14 prix
For Counter = 2 To LastRow Step 1
Sheets("step(2)").Select
Dim V, W, P
V = Range("J" & Counter).Value
W = Range("K" & Counter).Value
P = Range("I" & Counter).Value
If P < PBOP1 And V < 198 And W < 0.1 Then
Range("M" & Counter) = "0.44"
Range("N" & Counter) = "0.1"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Class Letter" 'option 1
ElseIf P < PBOP2 And V > 198 And V < 2206 And W <= 0.1 Then
Range("M" & Counter) = "0.66"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Class Large Letter" 'option 2
ElseIf P < PBOP3 And V > 198 And V < 2206 And W > 0.101 And W <= 0.25 Then
Range("M" & Counter) = "0.92"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Class Letter" 'option 3
ElseIf P < PBOP4 And V > 198 And V < 2206 And W > 0.251 And W <= 0.5 Then
Range("M" & Counter) = "1.24"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Class Letter" 'option 4
ElseIf P < PBOP5 And V > 198 And V < 2206 And W > 0.501 And W <= 0.75 Then
Range("M" & Counter) = "1.76"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Class Letter" 'option 5
ElseIf P < PBOP6 And V > 198 And V < 2206 And W > 0.751 And W <= 1 Then
Range("M" & Counter) = "2.36"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Packet" 'option 6
ElseIf P >= PlOP7 And P < PBOP7 And V <= 2206 And W <= 0.75 Then
Range("M" & Counter) = "3.31"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Recorded Packet" 'option 7
ElseIf P >= PlOP8 And P < PBOP8 And V <= 2206 And W >= 0.75 And W <= 1 Then
Range("M" & Counter) = "4.24"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Recorded Packet" 'option 8
ElseIf P >= PlOP9 And P < PBOP9 And V > 2206 And V <= 23200 And W <= 0.75 Then
Range("M" & Counter) = "3.31"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First recorded Packet" 'option 9
ElseIf P >= PlOP10 And P < PBOP10 And V >= 2206 And V < 23200 And W > 0.751 And W < 1 Then
Range("M" & Counter) = "4.24"
Range("N" & Counter) = "0.3"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "Royal Mail First Recorded Packet" 'option 10
ElseIf P > PBOP11 And V < 23200 And W < 1 Then
Range("M" & Counter) = "5.00"
Range("N" & Counter) = "0"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day ExpressPak" 'option 11
ElseIf V > 2206 And V <= 23200 And W > 1 And W < 5 Then
Range("M" & Counter) = "5.00"
Range("N" & Counter) = "0"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day ExpressPak" 'option 12
ElseIf V <= 23200 And W <= 1 Then
Range("M" & Counter) = "5.00"
Range("N" & Counter) = "0"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day Standard Parcel" 'option 13
ElseIf V >= 23200 And W < 1 Then
Range("M" & Counter) = "5.50"
Range("N" & Counter) = "0.7"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day Standard Parcel" 'option 14
ElseIf V <= 23200 And W > 1 And W <= 5 Then
Range("M" & Counter) = "5.00"
Range("N" & Counter) = "0"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day Standard Parcel" 'option 15
ElseIf V >= 23200 And W > 1 And W <= 5 Then
Range("M" & Counter) = "5.50"
Range("N" & Counter) = "0.7"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day Standard Parcel" 'option 16
ElseIf W > 5 And W <= 30 Then
Range("M" & Counter) = "5.50"
Range("N" & Counter) = "0.7"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD Next day Standard Parcel" 'option 17
ElseIf W > 30 And W <= 99 Then
Range("M" & Counter) = W
Range("N" & Counter) = "0.7"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD 2 days freight Parcel" 'option 18
ElseIf W > 99 And W <= 999 Then
Range("M" & Counter) = W
Range("N" & Counter) = "0.7"
Range("O" & Counter) = "JiffyBag"
Range("P" & Counter) = "DPD 2 days freight Parcel" 'option 19
End If
Dim Percentage
Percentage = Counter / LastRow * 100
Percentage = Format(Percentage, "#0.0")
Application.StatusBar = "The macro is actually in treatment " & Percentage & "Percent"
Next Counter
Range("M1:P" & LastRow).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Application.ScreenUpdating = True
CreateObject("Wscript.shell").Popup "The calculation is over", vbOKOnly, "Success", vbExclamation
'
End SubMerci d'avance pour vos reponses (toute critique est bonne a prendre, n'hesitez pas !)
Dernière édition:
