Insert blank row based on two criterias

[Boris7265]

Hi everybody,

I am trying to insert blank row based on two critieria and after searching I come up with the code below:

Range("A1").Select
Do Until ActiveCell.Value = Empty
Cells.Find(What:="BRO_CT" And "C02:Garnishment Filing Fee", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Offset(1, 0).Value = Empty Then
Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
Loop
Range("A1").Select

The problem it doesn't work ,If I delete second condition :"C02:Garnishment Filing Fee" the code works , but if I added second condition it doesn't work . I need these two conditons to be met for a row to be inserted.
Please, help will be gratly appreciated.

Best Reagrds,

BorisGomel

 

[saltkev]

Hi

Not 100% I understand the problem but give this a go it might solve the prob.

'Assumptions cells in Col "A" Could hold "BRO_CT" cells in Col "B" Could hold "Garnishment Filing Fee"

Application.ScreenUpdating = False
Dim searcher 'Referance Object
For Each searcher In Range("a1:a100")   ' Single col to Search Containing "BRO_CT"

    If searcher = "BRO_CT" And searcher.Offset(0, 1).Value = "Garnishment Filing Fee" Then 'Search Criteria
         
            searcher.Offset(1, 0).EntireRow.Insert ' Action When Criteria Met
            
    End If
    
Next searcher
End Sub

Regards

Kev

 

[Boris7265]

Good Morning Kev,

And thank you very much. Your code is working nilcely. The criteries are in columnn n and m , but I copy-paste these two columns onto columns a and b and it works very well.
Dear Kev, if it is not a lot toruble for you - I might need it later. So far, once the rows have been inserted onto worksheet , I do copy-paste the row which meets the criteria into blank row that wa just inserted. We print checks for big banks and they want us to print these checks twice.I have so far in spreadsheet around 20-30 rows to be copied, but we have increasing value a lot lately and consiquently a lot copy paste will be followed. How can code can be added to ixisted to copy-paste rows that meet criteria onto balnk row that had been just inserted.
Thank you for all your help.
The Best Regards,
BorisGomel

 

[saltkev]

Hi Boris

There is no need to copy and paste your data, just change the code the reflect where your first line of data is. Look for "For Each searcher In Range("a1:a100")" change the A1:A100 to N1:N100 if you have 200 rows it needs to change to 200.

Any how try this mod and see if it suits what you are looking for.

'Assumptions cells in Col "A" Could hold "BRO_CT" cells in Col "B" Could hold "Garnishment Filing Fee"

Application.ScreenUpdating = False
Dim searcher, searcher1 'Referance Objects
For Each searcher In Range("a1:a100")   ' Single col to Search Containing "BRO_CT"

    If searcher = "BRO_CT" And searcher.Offset(0, 1).Value = "Garnishment Filing Fee" Then 'Search Criteria
         
            
            searcher.Offset(1, 0).EntireRow.Insert ' Action When Criteria Met
            
                       
    End If
    
Next searcher

For Each searcher1 In Range("a1:a100")   ' Single col to Search Containing "BRO_CT"

    If searcher1 = "BRO_CT" And searcher1.Offset(0, 1).Value = "Garnishment Filing Fee" And searcher1.Offset(1, 0).Value = "" Then 'Search Criteria
         
            
            searcher1.EntireRow.Copy
            searcher1.Offset(1, 0).Select
            ActiveSheet.Paste
            
    End If
    
Next searcher1

regards

Kev

 

[Boris7265]

Thank you very much Kev!!!

The macro is working. It is absolutly amasing!!!

I am very thankfull for all you help.

The Best Reagrds,

BrosiGomel