mercredi 30 octobre 2019

excel VBA - inserting rows based on cell contents looped through all rows

I am trying to write a macro that tidies up and interrogates raw data exported from some analytical instrumentation. I would like it to look through one column (sample names) down all rows and look for indicators of specific sample types e.g. duplicates. Finding these indicators i want to insert a row, and in the new inserted row do some simple calculations based on the two rows above. For now i will just be happy getting the row insertion to work

Im a total newbie when it comes to VBA. I've brought a VBA for dummies book, but i'm struggling to see the light. I have cobbled together some code basically copied off google searches. I can get it to find the key word and insert 1 row, but it finds the first one and stops. There are multiple instances of these keywords in my data, and i want to insert a row below each

'original code - finds first keyword, inserts row and stops

Sub dup_finder()
    Dim colHeader As Range

    Set colHeader = Range("B1:B500")


Dim currCell As Range
Set currCell = Cells.Find("*_dup")

If Not currCell Is Nothing Then currCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown


End Sub

'my attempt to include loop - inserts 500 rows below keyword! stops 
after first instance

Sub dup_finder()
Dim colHeader As Range
Dim row As Long
Dim currCell As Range

Set colHeader = Range("B1:B500")

Set currCell = Cells.Find("_dup")
        For row = 1 To 500
If Not currCell Is Nothing Then currCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown

 Next row

End Sub

Aucun commentaire:

Enregistrer un commentaire