VBA en facturation

[tama10]

Bonjour,
J'ai un petit problème sur une macro.
Voilà mon code.
erreur 1004
(mon erreur et en gras.)
J'ai tt essayé en vain.
Si quelq'un pourrait m'aider...

Sub Macro1()
' Macro1 Macro
' Macro enregistrée le 22/05/2009 par VBACrumble
Dim s1 As Worksheet, s2 As Worksheet, tad, i&
Set s1 = Worksheets("VISU Facture")
Set s2 = Worksheets("Contenu facture")
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
tad = Array("E17", "C21", "G10", "G11", "G12", "H12", "D19", _
"B25", "H25", "I25", "J25", _
"B26", "H26", "I26", "J26", _
"B27", "H27", "I27", "J27", _
"B28", "H28", "I28", "J28", _
"B29", "H29", "I29", "J29", _
"B30", "H30", "I30", "J30", _
"B31", "H31", "I31", "J31", _
"B32", "H32", "I32", "J32", _
"B33", "H33", "I33", "J33", _
"B34", "H34", "I34", "J34", _
"B35", "H35", "I35", "J35", _
"J40", "J41", "J42")
Application.ScreenUpdating = False
s1.Visible = xlSheetVisible
s1.[E17] = Worksheets("creation Facture").[09]
For i = 0 To 50
s1.Range(CStr(tad(i))) = wf.VLookup(s1.[E17], s2.[A3:BB100], i + 2, 0)Next
s1.Activate
Application.ScreenUpdating = False
End Sub
Sub macro2()
Worksheets("VISU Facture").Visible = xlHidden
Worksheets("creation Facture").Activate
End Sub

 

[Roland_M]

Re : VBA en facturation

bonsoir
il manque les deux : avant le next
s1.Range(CStr(tad(i))) = wf.VLookup(s1.[E17], s2.[A3:BB100], i + 2, 0)Next

comme ceci
s1.Range(CStr(tad(i))) = wf.VLookup(s1.[E17], s2.[A3:BB100], i + 2, 0) : Next

 

[tama10]

Re : VBA en facturation

Il y avait une petite erreur le code original fait revenie next à la ligne.
J'ai éssayé kan mm ms sans effet.


Sub Macro1()
' Macro1 Macro
' Macro enregistrée le 22/05/2009 par VBACrumble
Dim s1 As Worksheet, s2 As Worksheet, tad, i&
Set s1 = Worksheets("VISU Facture")
Set s2 = Worksheets("Contenu facture")
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
tad = Array("E17", "C21", "G10", "G11", "G12", "H12", "D19", _
"B25", "H25", "I25", "J25", _
"B26", "H26", "I26", "J26", _
"B27", "H27", "I27", "J27", _
"B28", "H28", "I28", "J28", _
"B29", "H29", "I29", "J29", _
"B30", "H30", "I30", "J30", _
"B31", "H31", "I31", "J31", _
"B32", "H32", "I32", "J32", _
"B33", "H33", "I33", "J33", _
"B34", "H34", "I34", "J34", _
"B35", "H35", "I35", "J35", _
"J40", "J41", "J42")
Application.ScreenUpdating = True
s1.Visible = xlSheetVisible
s1.[E17] = Worksheets("creation Facture").[09]
For i = 0 To 50
s1.Range(CStr(tad(i))) = wf.VLookup(s1.[E17], s2.[A3:BB100], i + 2, 0)
Next
s1.Activate
Application.ScreenUpdating = True
End Sub
Sub macro2()
Worksheets("VISU Facture").Visible = xlHidden
Worksheets("creation Facture").Activate
End Sub

 

[Bebere]

Re : VBA en facturation

bonjour Tama,Roland

On Error Resume Next
For i = 0 To ubound(tad)'plus de 50
s1.Range(CStr(tad(i))) = wf.VLookup(s1.[E17], s2.[A3:BB100], i + 2, 0)
Next

ou avec une fonction qui gère l'erreur

Public Function retFind(NombreRecherché, PlageRecherche, NoColonne) As Variant
'Daniel M., mpfe
Dim f As Variant

f = Application.VLookup(NombreRecherché, PlageRecherche, NoColonne)
If IsError(f) Then
retFind = ""
Else
retFind = f
End If
End Function

que tu appelles comme suit

For i = 0 To ubound(tad)'plus de 50
s1.Range(CStr(tad(i))) =retFind(s1.[E17],s2.[A3:BB100],i+2)
Next

à bientôt

 

[JNP]

Re : VBA en facturation

Bonjour le fil 🙂,
Evite le multipostage 😡!!! Surtout qu'à priori, l'erreur est résolue sur l'autre post...
Bon WE 😎