Changer de colonne lorsque macro est finie

Question

Je souhaite afficher les résultats de la macro en colonne C, dès que j'ai fini ma macro. Puis en colonne D, dès que j'ai fini ma macro. Puis en colonne E, etc.;

1] Voici mon fichier xlsm.

8request-for-internship-agreement.xlsm (50.13 Ko)

2] Voici mon code

Sub Fill_in_form()

Dim starting_date_internship As String
Dim interval_type As String
Dim ending_date_internship As Date
Dim number_of_months_to_add As Integer
Dim objectives_and_missions_internship As String
Dim registered_company_name As String
Dim commercial_company_name As String
Dim VATno_number_company As Variant
Dim contact_number_company As Variant
Dim department_internship As String
Dim first_name_supervisor As String
Dim last_name_supervisor As String
Dim Gender_OptionButton1 As OptionButton
Dim gender_supervisor As String
Dim title_internship_supervisor As String
Dim email_adress_supervisor As String
Dim professional_phone_number_supervisor As Variant
Dim frequency_of_pay As String
Dim amount_of_pay_in_local_currency As Single
Dim currency_exchange_rate As Single
Dim presence_required_at_night_sunday_blank_holidays As String
Dim maximum_hours_present_per_day_in_the_company As Integer
Dim answer_travel_inside_country As String
Dim answer_travel_outside_country As String

'Do
  'starting_date_internship = InputBox("2/36: Enter starting date internship [DD/MM/YYYY]")
  'If Not IsDate(starting_date_internship) Then MsgBox "Enter a valid format for starting date internship DD/MM/YYYY"
'Loop While Not IsDate(starting_date_internship)
'Range("B2") = starting_date_internship
'interval_type = "m"
'number_of_months_to_add = InputBox("3/36: Enter number of months for the internship")
'Range("B3") = number_of_months_to_add
'ending_date_internship = DateAdd(interval_type, number_of_months_to_add, starting_date_internship)
'Range("B4") = ending_date_internship
objectives_and_missions_internship = InputBox("5/36: Enter objectives and missions during internship")
Range("B5") = objectives_and_missions_internship
'registered_company_name = InputBox("8/36: Enter registered trade name of the company")
'Range("B8") = registered_company_name
'commercial_company_name = InputBox("9/36: Enter commercial name of the company")
'Range("B9") = commercial_company_name '
'Do
    'VATno_number_company = InputBox("10/36: Please enter the VATno number of the company [Format 8 digits: 99999999]")
    'If Not Len(VATno_number_company) = 8 Then MsgBox VATno_number_company & " is not a 8 digit number"
'Loop Until Len(VATno_number_company) = 8
'Range("B10") = VATno_number_company
'contact_number_company = InputBox("15/36: Enter contact number of the company[+ 45 xx xx xx xx]")
'Range("B15") = contact_number_company
department_internship = InputBox("17/36: Enter department of internship")
Range("B17") = department_internship
title_internship_supervisor = InputBox("18/36: Enter title of supervisor [Mr. or Mrs.]")
Range("B18") = title_internship_supervisor
first_name_supervisor = InputBox("19/36: Enter first name of supervisor")
Range("B19") = first_name_supervisor
last_name_supervisor = InputBox("20/36: Enter last name name of supervisor")
Range("B20") = last_name_supervisor
If title_internship_supervisor = "Mr." Then gender_supervisor = "M" Else gender_supervisor = "F"
Range("B21") = gender_supervisor
job_title_supervisor = InputBox("22/36: Enter job title of supervisor")
Range("B22") = job_title_supervisor
email_adress_supervisor = InputBox("23/36: Enter email adress of supervisor")
Range("B23") = email_adress_supervisor
professional_phone_number_supervisor = InputBox("24/36: Enter professionnal phone number of supervisor [+ 45 xx xx xx xx]")
Range("B24") = professional_phone_number_supervisor
'frequency_of_pay = InputBox("25/36: Enter frequency of pay in local currency [daily, monthly or one-off payment]")
Range("B25") = frequency_of_pay
'amount_of_pay_in_local_currency = InputBox("26/36: Enter amount of pay in local currency")
'Select Case frequency_of_pay
    'Case "daily"
        'amount_of_pay_in_local_currency = amount_of_pay_in_local_currency * 5 * 24
    'Case "monthly"
       ' amount_of_pay_in_local_currency = amount_of_pay_in_local_currency * 24
    'Case Else
        'Otherwise amount_of_pay_in_local_currency = amount_of_pay_in_local_currency
'End Select
'Range("B26") = amount_of_pay_in_local_currency & "DKK"
Worksheets("Sheet1").Range("B27").Value = Worksheets("Converter").Range("C4").Value
currency_exchange_rate = Worksheets("Sheet1").Range("B27").Value
presence_required_at_night_sunday_blank_holidays = MsgBox("30/36: Presence required at night, Sunday, blank holidays?", vbQuestion + vbYesNo + vbDefaultButton2, "Message Box Title")
If presence_required_at_night_sunday_blank_holidays = vbYes Then
 Range("B30") = "Yes"
 Else
 Range("B30") = "no"
End If
maximum_hours_present_per_day_in_the_company = InputBox("32/36: Enter maximum hours present per day in company [enter flexible if flexible hours]")
Range("B32") = maximum_hours_present_per_day_in_the_company
answer_travel_inside_country = MsgBox("33/36: Business travel planned inside country of internship?", vbQuestion + vbYesNo + vbDefaultButton2, "Message Box Title")
If answer_travel_inside_country = vbYes Then
 Range("B33") = "Yes"
 Else
 Range("B33") = "none"
End If
answer_travel_outside_country = MsgBox("34/36: Business travel planned outside country of internship?", vbQuestion + vbYesNo + vbDefaultButton2, "Message Box Title")
If answer_travel_outside_country = vbYes Then
 Range("B34") = "Yes"
 Else
 Range("B34") = "none"
End If
End Sub

Merci de votre aide.

h2so4 · Answer

Ajoute cette instruction entre le dernier end if et le end sub columns("B:B").insert shift:=xltoright...

h2so4 · Answer

J'ai fait l'hypothèse qu'il n'y avait aucune donnée à partir de la colonne B. dans ce cas, ta macro met les valeurs en colonne B et quand la macro est terminée, l'ajout proposé insère une nouvelle colonne en colonne B, décalant ainsi le contenu de toutes les autres colonnes d'une colonne vers la droite. Si tu veux utiliser la macro alors qu'il y a déjà des données à partir de la colonne B. Pour la première exécution de la macro, insère manuellement une nouvelle colonne en B puis lance ta macro (qui contient l'ajout proposé)...