Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scan for combo then insert row
I have a worksheet sorted by Columns D, E, and G. I am trying to create a
macro that, starting in row 3, will scan every row in Columns D, E, and G for the following combination Column D = Sale Column E = Purchaser Column G = Solution When I find the last record of this combination (Column D/Sale), (Column E/Purchaser), (Column G/Solution) I would like to insert a row below the last combination. Can you help me? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scan for combo then insert row
Try the following.
Sub ScanCombinations() Dim i As Long Dim lngRow As Long With Sheets("Sheet1") lngRow = .Cells(.Rows.Count, "D") _ .End(xlUp).Row End With For i = lngRow To 3 Step -1 If Cells(i, "D") = "Sale" And _ Cells(i, "E") = "Purchaser" And _ Cells(i, "G") = "Solution" Then Rows(i + 1).Insert Exit Sub End If Next i End Sub -- Regards, OssieMac "JHopper" wrote: I have a worksheet sorted by Columns D, E, and G. I am trying to create a macro that, starting in row 3, will scan every row in Columns D, E, and G for the following combination Column D = Sale Column E = Purchaser Column G = Solution When I find the last record of this combination (Column D/Sale), (Column E/Purchaser), (Column G/Solution) I would like to insert a row below the last combination. Can you help me? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scan for combo then insert row
My previous code will work if it is the active sheet and also I forgot to
turn off CutCopyMode which will give problems if you happen to have copied something just before running the code. Use the following instead. Sub ScanCombinations() Dim i As Long Dim lngRow As Long Application.CutCopyMode = False 'Replace "Sheet1" with your sheet name With Sheets("Sheet1") lngRow = .Cells(.Rows.Count, "D") _ .End(xlUp).Row For i = lngRow To 3 Step -1 If .Cells(i, "D") = "Sale" And _ .Cells(i, "E") = "Purchaser" And _ .Cells(i, "G") = "Solution" Then .Rows(i + 1).Insert Exit Sub End If Next i End With -- Regards, OssieMac "JHopper" wrote: I have a worksheet sorted by Columns D, E, and G. I am trying to create a macro that, starting in row 3, will scan every row in Columns D, E, and G for the following combination Column D = Sale Column E = Purchaser Column G = Solution When I find the last record of this combination (Column D/Sale), (Column E/Purchaser), (Column G/Solution) I would like to insert a row below the last combination. Can you help me? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scan for combo then insert row
That's it; you nailed it! Thanks for the help OssieMac. I appreciate it.
"OssieMac" wrote: My previous code will work if it is the active sheet and also I forgot to turn off CutCopyMode which will give problems if you happen to have copied something just before running the code. Use the following instead. Sub ScanCombinations() Dim i As Long Dim lngRow As Long Application.CutCopyMode = False 'Replace "Sheet1" with your sheet name With Sheets("Sheet1") lngRow = .Cells(.Rows.Count, "D") _ .End(xlUp).Row For i = lngRow To 3 Step -1 If .Cells(i, "D") = "Sale" And _ .Cells(i, "E") = "Purchaser" And _ .Cells(i, "G") = "Solution" Then .Rows(i + 1).Insert Exit Sub End If Next i End With -- Regards, OssieMac "JHopper" wrote: I have a worksheet sorted by Columns D, E, and G. I am trying to create a macro that, starting in row 3, will scan every row in Columns D, E, and G for the following combination Column D = Sale Column E = Purchaser Column G = Solution When I find the last record of this combination (Column D/Sale), (Column E/Purchaser), (Column G/Solution) I would like to insert a row below the last combination. Can you help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert combo box in a cell | Excel Discussion (Misc queries) | |||
automatically insert date when I scan an item | Excel Discussion (Misc queries) | |||
How do I insert combo drop box and still be able to sort? | Excel Worksheet Functions | |||
Insert Values into combo box | Excel Programming | |||
Why can't I insert a Combo Box in Excel? | Excel Discussion (Misc queries) |