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?

0
Utf
2/10/2010 9:23:02 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
729 Views

Similar Articles

[PageSpeed] 25

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?
> 
0
Utf
2/10/2010 9:51:02 PM
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?
> 
0
Utf
2/10/2010 10:02:02 PM
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?
> > 
0
Utf
2/10/2010 10:55:01 PM
Reply:

Similar Artilces:

Insert comments default font setting
How do you change the default font setting for Comments that are inserted into a cell? You can change the default font size: Right-click on the desktop, and choose Properties On the Appearance tab, click Advanced. From the Item dropdown, choose Tooltip Choose a font Size, click OK, click OK or use a macro to insert a comment with specific formatting: http://www.contextures.com/xlcomments03.html#Formatted Jerry wrote: > How do you change the default font setting for Comments > that are inserted into a cell? -- Debra Dalgleish Excel FAQ, Tips & Book List http:/...

Can't insert a zip file
Every now and again outlook won't let me insert a zip file. When i go to insert i get open instead of insert. this only happens for zip files. If i close outlook and re-open it i can then insert a zip file. Any assistance in resolving this would be greatly appreciated. I am using Office XP Pro. Sounds like you are running Windows XP. Only way around it is to install something like WinZip, WinRar, .etc or de-register the the libraries (DLL files) that treat zips like folder. "scott" <scott.barber@pip.com> wrote in message news:000201c36aad$cdeccac0$a001280a@phx.gbl.....

Protecting and Row Heights
Is there any way to establish protection on cell content that woul allow you to adjust row height -- Message posted from http://www.ExcelForum.com Hi, I think if you go to Menu-Tools-protection-protect sheet, you can select the "format rows" checkbox. jeff >-----Original Message----- >Is there any way to establish protection on cell content that would >allow you to adjust row height? > > >--- >Message posted from http://www.ExcelForum.com/ > >. > I don't see that option. Is it because I'm still using Excel 97 -- Message posted from http:...

row height changing on own
I have a excel spreadsheet that is saved as a read-only file. My work area has access to this file. When one co-worker opens the file, the row height is different from everyone elses. Any suggestions why this would happen or how to fix? On Apr 17, 7:53 pm, cab58 <cabca...@gmail.com> wrote: > I have a excel spreadsheet that is saved as a read-only file. My work > area has access to this file. When one co-worker opens the file, the > row height is different from everyone elses. Any suggestions why this > would happen or how to fix? The actual row height is different? Or doe...

Possible to do a calc on certain rows?
Hi all, In 23 days time I am off around the world for 10 years. I have a pal TX and have found that I can use excel spread sheets. I already have budget set in MS ACCESS that is accessable online, but I would like budget in Excel so I can keep up to date as we travel around. In my budget I have the following columns (A1) Type (food, accommodation) (B1)Price (c1)Days (d1)Place then the following rows have the main information (A2)accommodation (B2)100.00 (c2)2 (d2)Auckland (A3) food (B3)60 (c3)2 (d3)Auckland (A4)Sightseeing (B4)40 (c4)1 (d4)North Shore Now is it possible to do somet...

Filter Combo Box Options based on other Combo Box Selection
Good morning, I really hope someone can help me with this, I am very new to Access I have a form a "Trade" form where the user using the database selects a client using a combo box (cboClient) [the available clients are populated in a table called tbl_Client], The next thing the user needs to is select a product using a combo box , however only certain products are available to certain clients (based on the table: tblCommission), therefore, I am trying to limit the visible options in the products combo box to just such products. I know that I need to therefore u...

Need Combo Box Help
I used the Forms Tool to setup a combo box Enter following data in B1:B3 B1 = yes B2 = no B3 = maybe Click Combo Box control from Forms Toolbar Right Click the Combo Box and then click Format Control On the control tab enter... Input Range: B1:B3 Cell Link: =INDEX(B1:B3,C1) No Workie... I want to be able to have the word "yes" or "no" or "maybe" show up in cell C1 when someone selects them from the combo box. Clearly I'm missing something. Thanks for any help. How about making your cell link D1 (just D1--not a formula). Then use this in C1: =INDEX...

How to create a combo box in a table
OK I will try and make this as detailed as I can I have a form in that form there is a sub form in a tab, that sub form would contain a data sheet that has a drop down Box , that drop down box selects a given product and up dates the price in one of the colums in the data sheet, by then entering your quantity it would then give you a total and that record apon hitting a command button would then be recorded. Here is my problem I am able to create a form based on a table, placing a combo box that will up date the text boxes with the selected info, but Im not able to get this proces int...

transpose a column into many rows
I have a file with one column as follows: Name Address Address2 City, State, Zip Name Address Address2 City, State, Zip etc. I need to transpose into columns for mail merge - so transpose the 4 rows into columns, then go down a line, transpose into columns, etc. so each address is on a new line. I tried using the TRANSPOSE function, but I can't get it. Any thought? Thanks! Assuming your data is in column A, starting a row 2, and all entries have exactly 4 rows, and there is a blank row between entries, In B2 put this formula: =OFFSET($A$2,(ROW()-2)*5+COLUMN()-2,0) Copy it acr...

? avoid changing sum function as rows added?
Hello Group: I'm obviously new. To keep it simple, here's how I'll pose the question: Suppose I have multiple rows and two columns. Column A contains names of people Column B contains each person's age The last cell in column B is to be an average of the ages, so in that cell I insert the function =AVERAGE(B1:B100) This works fine, but as I add rows, I have to change the formula to include the recently added row, B101, then B102, etc) Is there any way I can make the function automatically include the last cell? Sort of like a wild symbol? It's not a big problem, but I...

Line chart for every 5th cell in a row
I have no idea how to do this, i cannot select individual cells manuall because there is too much data and i want it to pickup every 5th cell i the row. I want one range on the line chart to plot B17, G17, L17..... etc. Then i want the next range to do D17, I17, N17.....etc So going up 5 letters of the alphabet at a time. One starting at B on starting at D Any ideas? Cheers Ke -- bluenos ----------------------------------------------------------------------- bluenose's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2616 View this thread: http://www.excelfor...

Scan Code Question
Would it be possible to utilize the scan code option for tenders to automatically select the correct credit card type simply by swiping the credit card? In other words, would it be possible to hit F12, swipe the card twice, once to select tender, and once to populate the cc info fields? ...

Inserting a picture into my publication blocks the text I have on
All I am trying to do is insert a picture onto my publication. I have my logo at the top and I am trying to insert a picture underneath it. Every time I insert the picture it blocks out the text on that portion of the page. I have tried everything and nothing works. I have tried making it transparent, etc. etc. I know how to put a picture in a picture frame and/or a text box, but I can't for the life of me figure out why it is blocking the text up above it. Someone please help..... Thx. Right click the picture, click order, and click Send to Back. -- Computing should be abou...

Access 2003 post SP3 hotfix
"Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install Office 2003 Service Pack 3." And a few more problems SP3 introduced. Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 http://support.microsoft.com/kb/945674 Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog...

Duplicated rows
I have a data table with 5000 rows, yet many rows are duplicated. Is there anyway to get rid of them quickly? Chirs -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25673 View this thread: http://www.excelforum.com/showthread.php?threadid=391262 This thread may help you http://www.excelforum.com/showthread.php?t=390910&highlight=duplicat -- Paul Sheppar ----------------------------------------------------------------------- Paul Sheppard's Profile: http://www.exc...

Inserting a cell value from an "IF" function
I want to do something that confuses me to explain, but I'll give it a go. I need to be able to insert a name from one worksheet onto another, depending on a letter in another column alongside the name. I need to be able to do this for several names in a list. I wonder if this is possible as I have no real idea where to start :) Thanks! -- -Liam >I have no real idea where to start :) Start here: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "liamellis91" <liamellis91@discussions.microsoft.com> wrote in message...

Combo boxes in forms
I am using Access 2007. I have several tables created for Purchase Order and a Purchase Order subform. The purchase order has the purchase order subform embedded in it. I have several combo boxes in the form. The combo boxes are for supplies and shipping methods. When I open the combo box and select the supplier and/or shipping method and close the form the info is saved for that particular purchase order. When I open the form again, the info is displayed as it should be. When I go to the underlying table that should be storing all the information under the suppliers and shipping...

insert single absolute symbol in formula
Hi, I am rolling over the spreadsheets for the new year and wonder if there's an easy way to insert a single absolute into the formula. i.e. if I highlight the formula and press F4 it puts absolutes in for both the column and the row reference, whereas I only want to change the column to absolute. Any ideas? Keep on pressing the F4 key. It'll cycle through all 4 possibilities--absolute row and column through relative row and column. sarahds wrote: > > Hi, > I am rolling over the spreadsheets for the new year and wonder if > there's an easy way to insert a single ab...

Copy to last row macro
Can someone look at this macro that I copied from a website and tell me why the code is not working for me. My source data work sheets is named "Input KPI" and my destination worksheet is named "KPI Dash2". I get a sub or function not defined error when I try to run and it seems to highlight the last row code. I have limited knowledge of VBA so maybe I am doing something wrong. I need to copy only non-blank cells in a certain range and have them copy to another worksheets first empty row in a certain range Not sure if this code will do that. Here is the code I was trying...

Combo Box and First Record
Hello, I based my combo box on a query that is sorted and my Table is updating but when I open my form, there is information in all the fileds except the field with my combo box. When I select an item from the combo box, everything fills in correctly but I want the form to be completely blank when it is open, open on a new record? Thanks again Set the Data Entry property of the form to Yes. -- Lynn Trapp MCP, MOS, MCAS "Lovespar" wrote: > Hello, I based my combo box on a query that is sorted and my Table is > updating but when I open my form, there is...

Inserting Rows...but losing "FORMAT"
I have a spreadsheet that I have used for 5 years...with daily data being inputted (in newly inserted rows), then Grand Totaled at the bottom of the spreadsheet. I have never had a problem...UNTIL RECENTLY. One of my columns is the time of day, such as 2:54pm, and presently, all of my inserted rows, except for only 3 of them, depict the time as 2:54 (WITHOUT THE AM or PM), so, I use the Format Painter, and I Fix the problem...that is, until the next day, when the same incident again occurs. I do not know why this is happening, but I must use this extra step (Format Painter) ...

Insert hyperlink with another application
I have a excel spreadhseet containing a hyperlink column, showing the path to a jpeg file. When I click on the hyperlink I would like it to open the jpeg file using an imaging application. I would like to repear this for a vast amount of data so that I don't have to insert objects for each individual record. Any response would be appreciated. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You can use the HYPERLINK Worksheet Function if your .jpg f...

Combo Boxes 11-13-07
I have several combo boxes on a form. One shows company names. I would like the combo box below the company name to show only my contacts for the company. My next combo box is for brokers. I have a combo box that shows broker company names. I want the combo box below to show only the contacts for the broker shown above. Can someone help me with this problem? -- Lisa S. Lisa, Try searching for "cascading comboboxes" in this NG. This question is asked many times and responded to many times. Apologies for the shortcut... hth -- Maurice Ausum "Lisa" wrote: > I ha...

Unselecting columns or rows..
Hi, How does one unselect a whole column or row from a selection? Just ctrl or shift clicking on that column or row doesn't work. Ctrl shift will add new columns and rows to a selection set, however. Thanks, Farhan. Farhan, Unfortunately, the answer is "Start Over"..... HTH, Bernie MS Excel MVP "Farhan Ata Arain" <j_kirk65Z@yahoo.com> wrote in message news:Owa$L%234qEHA.3976@TK2MSFTNGP10.phx.gbl... > Hi, > > How does one unselect a whole column or row from a selection? Just ctrl or > shift clicking on that column or row doesn't work. Ct...

make a listing of each cell made up of its row and column
I have a grid of x number of rows and y number of column. I want to make a listing of all the cells in thish grid by using a formula without having to enter the cell row and column numbers manually. How do I do this ? If you mean extract all the elements in a grid into a column elsewhere, one way .. Suppose your grid is in Sheet1, B2:D6 (a 5R x 3C grid with 15 elements) In Sheet2 ------------- Put in any starting cell, say in C3: =OFFSET(Sheet1!$B$2,INT((ROW(A1)-1)/COLUMNS(Sheet1!$B$2:$D$6)),MOD(ROW(A1)-1 ,COLUMNS(Sheet1!$B$2:$D$6))) Copy C3 down to C17 (as there are 15 elements) or j...