View original (French)

VBA - Macro to select a row based on conditions

Solved
jeannuslanus Posted messages 15 Registration date   Status Member Last intervention   -  
jeannuslanus Posted messages 15 Registration date   Status Member Last intervention   -
Bonjour,

I need to create a macro as part of my internship that searches for a value in column A of the sheet "suivi renego groupe". This value depends on the cell C2 of another sheet titled "Saisie". If the program finds the value in "suivi renego groupe", it selects the first 13 cells of the corresponding row and copies them.

So far, here is what I have managed to do, but I am a beginner, so it is still difficult for me and it doesn't work!

'search macro
Sub recherche()
Sheets("Saisie").Select derlig = Range("C2").End(xlUp).
Row Sheets("Suivi Renego Groupe").
Select Range("A2").Select
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = derlig Then Rows(i).Cut Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2)
Next

End Sub
Thank you very much in advance for your help!

7 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,
    And where do you copy them?

    --
    Michel
    0
  2. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
     
    I copy them transposed in a list in the "input" tab starting from cell C2!
    Thank you very much!
    0
  3. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
     
    It's great, thank you very much! However, the copy-paste is done online; how to modify the program to rather make a "transposed cut-paste" where the cut line disappears?
    That would be really super!!!! :D
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      I had read it wrong, excuse me

      I made you a macro but you could get the same result more simply with the VLOOKUP formula
      if you don't know this beast
      https://www.commentcamarche.net/faq/s/recherchev

      Sheets("saisie").Range("D2:O2") = .Range(.Cells(Lig, "B"), .Cells(Lig, "M")).Value
      becomes
      Sheets("saisie").Range("C3:C14") = Application.Transpose(.Range(.Cells(Lig, "B"), .Cells(Lig, "M")))
      0
  4. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
     
    It's amazing, it works wonderfully well!!
    Thank you from the bottom of my heart!!
    One last thing, if you have a few more minutes to spare:
    I created a macro that works very well, the principle is that in the "saisie" sheet we can add a row to the table "suivi renego groupe".
    Thanks to the "copy" program you just gave me, I can now fill in the ranges of "saisie" simply from the data I enter in cell C2, which the macro will search for in the "suivi renego groupe" sheet.
    The idea would be to be able to delete the row if the value of C2 already exists in the "suivi renego groupe" sheet to "save" the data that I enter in the "saisie" sheet.
    Would it then be possible to modify the following program to do that? Is it clear enough for you?
    Sub enregistrer()
    '
    ' Record Macro
    '
    Sheets("Saisie").Select
    Range("C2:C15").Select
    Selection.Copy
    Sheets("Suivi Renego Groupe").Select
    Range("A2").Select

    Do While Not IsEmpty(ActiveCell.Value)
    ActiveCell.Offset(1, 0).Activate
    Loop

    Range("A81").Select
    ActiveWindow.SmallScroll Down:=9
    Sheets("Saisie").Select
    Selection.Copy
    Sheets("Suivi Renego Groupe").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    End

    End Sub

    In short, an alternative should be added along the lines of "this program applies regardless, but if the value of cell C2 in the 'saisie' sheet appears in column A of sheet C2, then the corresponding row should be deleted".

    Thank you very much in advance!
    0
  6. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Is it clear enough for you?

    Absolutely not

    --
    Michel
    0
  7. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
     
    You simply need to add this function to my program:

    If the value in cell C2 of the "saisie" sheet exists in the "suivi renego groupe" column, then the corresponding row in the "suivi renego groupe" should be deleted.
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      What is the purpose of what I suggested to you?
      0
    2. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
       
      L'objectif est de mettre à jour des données en passant par une feuille de saisie : on recherche des informations qui sont dans le tableau "suivi groupe renego", à partir d'une donnée que l'on entre dans la feuille de saisie et elles s'affichent dans la feuille de saisie, on les modifie et elles s'enregistrent dans le tableau "suivi groupe renego". Pour qu'elles s'enregistrent, j'ai fait ce programme : ```vba Sub enregistrer() ' ' Enregistrer Macro ' Sheets("Saisie").Select Range("C2:C15").Select Selection.Copy Sheets("Suivi Renego Groupe").Select Range("A2").Select Do While Not IsEmpty(ActiveCell.Value) ActiveCell.Offset(1, 0).Activate Loop Range("A81").Select ActiveWindow.SmallScroll Down:=9 Sheets("Saisie").Select Selection.Copy Sheets("Suivi Renego Groupe").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True End End Sub ``` Mais les valeurs s'enregistrent à la suite comme des nouvelles valeurs sans écraser les précédentes. Je voudrais donc qu'elles continuent de s'enregistrer à la suite en supprimant les anciennes lignes. Pour cela, il faudrait compléter le programme ci-dessus avec une fonction qui supprime la ligne où la donnée C2 (de la feuille de saisie) apparaît dans la colonne A (de la feuille "suivi renego groupe"). Je sais, c'est assez complexe, mais mon boss n'a pas compris que j'étais en stage de com et pas de VBA... Si vous réussissez à comprendre, ce serait vraiment génial !!
      0
    3. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
       
      But don't worry, what you gave me is already super useful!!
      0
    4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Well, it's the same the other way around :o) 
      Option Explicit
      '------
      Sub save()
      Dim Value, Row As Integer
      Value = Sheets("input").Range("C2")
      With Sheets("Monitoring Renegotiation Group")
       Row = .Columns("A").Find(Value, , , , , xlPrevious).Row
       Range(.Cells(Row, "B"), Cells(Row, "N")) = Application.Transpose(Sheets("input").Range("C3:C15"))
      End With
      End Sub
      0
    5. jeannuslanus Posted messages 15 Registration date   Status Member Last intervention  
       
      Thank you, that's great! However, I have an error message regarding the line Range(.cells.....etc. Do you know why?
      Thanks again, it's really helping me!
      0
  8. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Option Explicit
    '---
    Sub copy()
    Dim Value As Variant, Row As Integer

    Value = Sheets("input").Range("C2")
    With Sheets("tracking renegotiation group")
     On Error GoTo empty
     Row = .Columns("A").Find(Value, , , , , xlPrevious).Row
     Sheets("input").Range("D2:O2") = .Range(.Cells(Row, "B"), .Cells(Row, "M")).Value
    Exit Sub
    empty:
    MsgBox Value & " unknown", vbCritical
    End With
    End Sub

    Michel
    -1