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 -
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!
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
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
That would be really super!!!! :D
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")))
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")))
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!
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!
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.
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.
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 !!
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
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