Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do. Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest. Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN. There could be 100 or 1000 transactions generated based on the day.
View Replies!
View Related
Select Based On 2 Search Criteria
I have an Excel Sheet with three columns of data. In a separate worksheet, I am setting up some calculations. To do so, I am having the user select from a drop down list, the data in the first and second columns. Based on the two values selected by the user, I would like to bring forward the value in the third column. Can someone please help me out with the formula to do so?
View Replies!
View Related
Using VBA To Select A Row And Colour Code It Based On Conditions
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something! I have a spreadsheet where column O has five conditions: 1 PO in 2 High Probability 3 Medium Probability 4 Low Probability Blank I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA. However, I'm having difficulty colouring the whole row; how do I do this? e.g. what I need to do is: If O2 = "1 PO in"; Select A2:T2; Colour green In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
View Replies!
View Related
Programming A Macro To Select Row Ranges Based On A Formula
I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document. If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:
View Replies!
View Related
Move Row Based Off Criteria
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this: Sub testmove() Dim c As Long For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5 If Cells(c, 6) = "Closed" Rows(c).Cut End If Next c.......
View Replies!
View Related
Repeat Row Based On Text Criteria
to run a macro or any other methods to repeat the same head rows in my database as followings: Let's say the main head row is in row 1. Item names such as Item A; Item B; Item C ....in column A What I want is to insert the same head row after the list of each item. Item name (head row) Book A Book A Book A Item name (head row to be repeated) Book B Book B Item name (head row to be repeated) Book C Book C
View Replies!
View Related
Delete Rows Based On Two Criteria Within Each Row
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool. The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error). The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...). What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as: "T" "[" "T" "#" "T" "@" "T" "]" The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ). I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
View Replies!
View Related
Delete Row Based Upon Multiple Criteria
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this: -----Column A-----------Column B--- 1.-- Computer_X ------- Software_A 2.-- Computer_X ------- Software_B 3.-- Computer_X ------- None 4.-- Computer_Y ------- None 5.-- Computer_Z ------- Software_A 6.-- Computer_Z ------- None I would like to delete the entire row if: 1) Column B entry is "None" AND 2) Column B has entries other than "None" that match Column A So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A. Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
View Replies!
View Related
Combining Row Results Based On Specific Criteria
If I have the following table: Name ProductSize QuantityPrice per unit AX35510 AX451015 AX551210 BX601010 AX35510 I now want a resulting table that will combine all rows where name, produc, size and price are identical and have one row with the same data apart from the quantity that will have been added - so for the table above the resulting table will be: Name ProductSize QuantityPrice per unit AX351010 AX451015 AX551210 BX601010
View Replies!
View Related
Copy Row Data To Another Worksheet Based On Criteria
Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
View Replies!
View Related
Finding Data Based On Row & Column Criteria
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX Result data(criteria base on Month & type) JanApril BananaXXXX Apple Orange
View Replies!
View Related
Select Row Where Only 1 Cell Is Selected
I need to delete part of the row where one cell is selected. If more than 1 cell is selected, I want a msgbox. Example: I have any cell selected on row 26. I want to select all cells in this row including column C thru column AD. Column A & B are not to be deleted. After the selection is made, I want the row deleted and the below cells moved up.
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select The Last Non-null Cell In A Row Or Column
I am finding that I am using the 'go to' option frequently in my macros, but I feel very limited by the options that I have (I need to learn VB-I know) I would like primarily to discover how to select the last cell containing anything (not including blanks like the 'last cell' option in the edit>go to) within a selected row. Ideally, I would like a formula to fill downward a given amount of rows so as to prevent myself from having to fill them all the way down to the end of the spreadsheet (therebye making the file to large). Also, If anyone could guide me to a resource at which I can find similar commands.
View Replies!
View Related
Macro To Select Cell After Last Row In Column
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case) (I know I can record a macro but the number of rows can potentially differ every time I run the macro) N.B. Excel version 2003
View Replies!
View Related
Macro: Select Cell A Of Selected Row
Currently working on a project for university and still learning how to use macro. Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
View Replies!
View Related
Select Multiple Worksheets Based On Cell
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was Sub Selects() Dim strSht As String Sheets("Criteria").Select strSht = ActiveSheet.Range("L31") Sheets( Array(strSht)).Select End Sub
View Replies!
View Related
Select Sheet Based On Cell Content
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the sheets( range("c5")).select to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
View Replies!
View Related
VBA Code To Select Last Cell In Row With Data
I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work.
View Replies!
View Related
Find Next Empty Row And Select Cell In Column A
how i can go about finding the next empty row (and select the first cell of that row (column A))? edit: It probably should be noted that there are cells in Columns A through P. There are rows where all and/or just one cell contains data per row. So i cannot use a " lookup" based on a single column.
View Replies!
View Related
Macro To Select Dates Based On Cell Values
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive. H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page. Below is what my limited understanding of VB came up with. Sub Date_Range() Dim First As Date Dim Last As Date First = Range("H4").Value Last = Range("I4").Value Sheets("Graph Data").Select Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _ , Criteria2:="
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data. However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View Replies!
View Related
Adding Data By Row Based On Typing In Cell Contained In Row
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more. Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc. Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
View Replies!
View Related
Format Row Based On Single Cell Value In That Row
I have a macro that's supposed to see if cell 5 meets criteria and continue to all sheets in workbook, except " Total". When I activate the macro on a specific page, it runs smoothly on that page. But when it's finished, it doesn't continue to the next sheet in the workbook. Sub ColourBG() Dim ws As Worksheet Dim line As Integer endline = Range("A1000").End(xlUp).Row For Each ws In Worksheets If ws.Name <> "Total" Then With ws For line = 3 To endline Application. ScreenUpdating = False If (Cells(line, 5).Value = "0206") Then _ Cells(line, 1).EntireRow.Font.ColorIndex = 5 '*(Blue) Next line Application.ScreenUpdating = True End With End If Next ws End Sub
View Replies!
View Related
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color. Attached is an sample of what i want.
View Replies!
View Related
Change Cell Value Based On Criteria
I have a series of data that I am trying to process based on various criteria and though this site I have solved most of the (simple) problems I had. I have one last problem to solve. I need to add the previous cell to the next one based on a cell value in a different column. Example: I want to add F2 to F3 if H3=3 but I need it to work so that if H4=3 it will add F3 to F4 and F3 now equal the new value i.e. (F2+F3). If H5 doesn't = 3 then F5=F5. I need the output to be a value not just an equation as I plan on going back and deleting all rows with H=3 in them. P.S. I don't mind if the output of the calculation of the F column is to a new column or overwrites F. Overwriting would be easier for when I go back through and delete rows based on H=3.
View Replies!
View Related
Parse A Cell Based On Criteria
I want to parse a cell if a date is present for example I have 03/26/1985 AL in one cell and want to parse to two different cells however in between those cells I have other cells with different data that I dont want to parse. Basically I need to parse the cells only if a date is present.
View Replies!
View Related
Copy And Paste Row By Cell Value Criteria
I am trying to copy and paste Row by cell value. It is working fine when I am copying row from one worksheet by comboBox value to other worksheet. But problem is after updating data when I want copy back by cell value, it is showing "Run-time error '1004'" - Application-defined or Object-defined error. the code below is working fine Private Sub CommandButton1_Click() Dim sfind As String Dim cl As Range 'check for a value & show message Sheet = "Data" Sheets(Sheet).Select Application. ScreenUpdating = False If Me.userIDCombo.Value = "" Then 'the textbox in userform Me.userIDCombo.SetFocus MsgBox "Please enter a Name to Update." ' show message if no Name Selected Exit Sub Else sfind = Me.userIDCombo.Value.........................
View Replies!
View Related
Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View Replies!
View Related
Populate Cell Based On Multiple Criteria
I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense... That being said: what I am looking to do is change cell L3 based on new criteria in cell M3. Right now if B3=Stationery World then L3=Stationery. What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.
View Replies!
View Related
SUM Based On A Criteria And Another Cell Being Blank
I need to create a formula that counts up the number of times a specific word is put into a cell lets say 'Carpet' only when another cell is left blank. Count the number of times between C1:10 the word 'Carpet' is input only when D1:10 is left BLANK? If looked into DCOUNTA formulas and SUMPRODUCT formulas but seem to be clutching at straws. My problem seems to be at referring the formula to the BLANK cell?
View Replies!
View Related
|
TRACKER
