VBA petite correction Fermé

Question

Bonjour à tous, je me pose une petite question, j'ai un code VBA qui fonctionnait parfaitement, voici la partie conscernée : 

Dim mydate As Date
myname = Worksheets("Face to face").Range("C2")
myfirstname = Worksheets("Face to face").Range("C3")
mydate = Worksheets("Face to face").Range("S2")
mymonth = Format(mydate, "mm")
myfile = Year(mydate) & " " & mymonth & " face to face" & " " & myname & myfirstname
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile

et je voulait y apporter une petite modification : 

Dim mydate As Date
myname = Worksheets("Face to face").Range("C2")
myfirstname = Worksheets("Face to face").Range("C3")
mydate = Worksheets("Face to face").Range("=SI(S3="";S2;S3)")
mymonth = Format(mydate, "mm")
myfile = Year(mydate) & " " & mymonth & " face to face" & " " & myname & myfirstname
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile

cependant il ne fonctionne plus apres avoir effectué cette modification, est ce que l'un de vous a une idée de la méthode a utiliser?

Merci d'avance a tous

Unombre · Answer

Bonjour,

Dim mydate As Date
myname = Worksheets("Face to face").Range("C2")
myfirstname = Worksheets("Face to face").Range("C3")
if Worksheets("Face to face").Range("S3") = "" then
mydate = Worksheets("Face to face").Range("S2")
else
mydate = Worksheets("Face to face").Range("S3")
mymonth = Format(mydate, "mm")
myfile = Year(mydate) & " " & mymonth & " face to face" & " " & myname & myfirstname
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile 


Si j'ai bien compris ton soucis, tu peux essayer ca !

Patrick · Answer

Yeahh Je te remercie enormement, tu a parfaitement compris ce que je voulait et j'ai parfaitement compris le principe donc tout est parfat
Merci 

une toute petite correction pour les personne qui pourait nous lire il manque le "end if"  ce qui donne 

Dim mydate As Date
myname = Worksheets("Face to face").Range("C2")
myfirstname = Worksheets("Face to face").Range("C3")
If Worksheets("Face to face").Range("S3") = "" Then
mydate = Worksheets("Face to face").Range("S2")
Else
mydate = Worksheets("Face to face").Range("S3")
    End If
mymonth = Format(mydate, "mm")
myfile = Year(mydate) & " " & mymonth & " face to face" & " " & myname & " " & myfirstname
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile


Encore merci!

Archer · Answer

Bonjour
pour éviter de taper le Worksheets("Face to face") a chaque ligne

Sub Macrodate()
Dim mydate As Date
   With Worksheets("Face to face")
      myname = .Range("C2")
      myfirstname = .Range("C3")
         If .Range("S3") = "" Then
            mydate = .Range("S2")
         Else
            mydate = .Range("S3")
         End If
   End With
mymonth = Format(mydate, "mm")
myfile = Year(mydate) & " " & mymonth & " face to face" & " " & myname & " " & myfirstname
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile
End Sub

A+
Maurice

charles · Answer

Je propose ce code VBA :

Option Explicit

Sub Essai()
  Dim myname As String, myfirstname As String, mydate As Date, myfile As String
  With Worksheets("Face to face")
    myname = .[C2]: myfirstname = .[C3]: mydate = Cells(3 + (.[S3] = ""), "S")
  End With
  myfile = Format(mydate, "yyyy mm") & " face to face" & " " & myname & " " & myfirstname
  ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile
End Sub

Les lignes #5 et #6 doivent être sur une seule ligne ;
idem pour les lignes #8 et #9 ; et pour #11 et #12.

Le corps de la sub est donc sur 6 lignes.

--------------------------------------

Le même code sans les 2 variables myname et myfirstname :

Option Explicit

Sub Essai()
  Dim mydate As Date, myfile As String
  With Worksheets("Face to face")
    mydate = Cells(3 + (.[S3] = ""), "S")
    myfile = Format(mydate, "yyyy mm") & " face to face" & " " & .[C2] & " " & .[C3]
  End With
  ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & myfile
End Sub

Les lignes #8 et #9 doivent être sur une seule ligne.
Ce code VBA est encore plus court !  ;)

VBA petite correction

4 réponses

Discussions similaires