If Text Found In Cell Then Place A 1 In Another Column
What I would like to do is if a string e.g. "*12/18/09*" is found in column A, then place "1" in column B. There are many rows that I would like to search. Either a formula or VBA would be fine.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find Text/Value & Use Found Cell, Row, Column In Macro Code
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is Dim XColumn As Range ‘the cell containing the text “X” Cells.Select Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Set XColumn = ActiveCell I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
View Replies!
View Related
Extract Text From Column :: Place Into Separate Columns
I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell. ** SWITCHLIST FOR TRAIN---Grain Spec -East DEPARTURE TIME from WESTTOWN is 01:00 TOWN STOP---SEAPRT TWN Arriving at 01:30 PICKUPS Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd. Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd. Train should leave this town with 2 car(s) TOWN STOP---PRARIE TWN Arriving at 02:00 SETOUTS Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay 1/05/2007 8:57:52 a.m. ...............................
View Replies!
View Related
Find Text & Offset By Found Value Row And Column
I'm writing some code where I need to know the column in which a specifc text string occurs. Because of the nature of the sheet, the string will only appear in one column, but that could be in almost any column on the worksheet. For example say the text string is "Year 1", I need to find the column with this string, and then offset down to a predetermined row, or offset over to the appropriate column (something like below). With Selection .Offset(0, x).Select If List.Selected(0) = True Then y = i - 1 With Selection .Value = Salary * Inflation1 ^ y End With Else
View Replies!
View Related
Searching A Column For Specific Text To Return A Number Of Items Found
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ... the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View Replies!
View Related
Copy Column Of Found Cell
i'm having trouble with some of the codes... i'm suppose to create a code that searches a specific phrase/word in a row, then from that row, i want to mark the entire column, so that i can copy it and paste it in a new sheet eg. find 'NE_KENNUNG', once it's found, mark the whole column "C"(the column may vary), copy the whole column, paste on to a new sheet here's what i've written so far.. ------------------------------------------------------------- Private Sub CommandButton1_Click() Search ("NE_KENNUNG") End Sub Sub Search(strSrch As String) Dim xFind MsgBox "in Suchfunktion !" If strSrch = "" Then Exit Sub
View Replies!
View Related
If Date Found In Cell Above, Put This Text In Comment
Cell B4 is selected by user and then runs the macro. The macro then looks UP (A3, A2, A1 etc) cell by cell until it finds the first cell that has a valid date in it and if that date is same as the date that the macro is being run pastes "This Text" into the comments section (Not replacing everything in the comments but simply adding to it.) Column A 2/20/2009 (empty cell) Dave Jonathan Steve 2/21/2009 James Sally Tyler (empty cell) John Betty
View Replies!
View Related
Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro. My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like. Here is the code I presently have for this part of my macro. Sub GetOpenFileName() strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file") If strFind = vbNullString Then Exit Sub With Sheets(strSheetsMainCompProfile) If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then MsgBox strFind & " cannot be found on this sheet" Else
View Replies!
View Related
Auto Color Whole Column When Found Text "Sunday"
The code below works well also. However, I got 2 problems here. First, I manage to find the word " Sunday", then color the cell below that Sunday. However, I need Excel auto find out Sunday and color that particular columns for me, for example, Sunday found and color start from cell(L55) below that Sunday up to more cells(L59), Range("L55:L59"). Second, I only manage to find first Sunday, I wish the system keep find and keep color untill it found that rows blank, so I using this code. 'Encountered blank cell in row 2, terminate search If Len(Cells(2, LColumn)) = 0 Then MsgBox "No matching date was found." Exit Sub I attached the excel files with sample
View Replies!
View Related
If Cell Contains Text Place Text In Another Cell
This might not actually be able to be done, but im sure the best chance I have to do this is by getting help from you all. What I need to do is look in cell "A1". If that cell contains a number I need to go to cell "B1" and type with the 00 being replaced with what is in cell "A1". For Example if cell "A1" has the number 67 in it then I need B1 to say .
View Replies!
View Related
User Box Options If Cell Entry Is A Duplicate Found In A Table Column
I have a table that i use for a customer database. and the end user adds new customers to this table, what i would like is for a message box to pop up whenever cell B2 matches an entry in a column in the customers table. the table starts on row 25, and the column i would like to check for duplicates is column B. I would like the message box to give the user the message "A customer by this name already exists, Would you like to load this customers file?" If the answer is yes, then the row that the match was found on would be copied and pasted onto row 1. if the answer is no, then nothing else happens. I hope this makes sense, i am posting this sheet of my workbook for reference.
View Replies!
View Related
Macro To Copy And Place Data To Specific Place
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View Replies!
View Related
Extract Numbers From Specified Place In Text String
I have got cell A1 containing this text string: =IF(SUM('SL-001 - AT-001-001'!R[852]C:R[856]C)=0,SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C, 'SL-001 - AT-001-001'!R[840]C:R[844]C,'SL-001 - AT-001-001'!R[846]C:R[850]C), SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C,'SL-001 - AT-001-001'!R[840]C:R[844]C, 'SL-001 - AT-001-001'!R[846]C:R[850]C,'SL-001 - AT-001-001'!R[852]C:R[856]C)) *'SL-001 - AT-001-001'!R992C*R3C9 and I would like a macro that will extract the numbers between each instance of the letters R and C , i.e. 852, 856, 826 etc etc. in cells A2, A3, A4 respectively.
View Replies!
View Related
Place Average Of The Five Numbers In Column
Column B values are the average of the most recent five values in column A. The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7? Note: I just want to be able to pick up the most recent average value – the method whereby it is done is of no consequence. However I cannot think of an alternative. Example:.....
View Replies!
View Related
Locking Row/column Headings In Place
The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns. As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles. Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?
View Replies!
View Related
AutoFilter Via Macro & Place Text In Visible Cells
I need a macro that can change the text in Field 46 from Criteria1 to something else, ie from "To be capped" to "Capitalised". This is my current Selection. AutoFilter Field:=39, Criteria1:="OPEN" 'STATUS Selection.AutoFilter Field:=46, Criteria1:="To be capped" 'TYPE Range("AT1").Select how to change the text only on the selection found by AutoFilter?
View Replies!
View Related
Text Search Returns Cell Text Contents Of Different Column In The Same Row
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found. A prepopulated worksheet has the text "gold" entered in cell T278. 1. user searches for "yellow_metal" 2. Excell finds "yellow_metal" in row 278, say in cell A278. 3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example. 4. Excell returns "gold"
View Replies!
View Related
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1. A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
View Replies!
View Related
Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie. | ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+---> | ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+---> Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID # The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View Replies!
View Related
Find Text And Copy Found Range
I have some EXCEL files, titled as "AAA", "BBB", "CCC", so on... Calculated values are placed in Column Z for each file. In order for me to export the files to another software, I would need to create another new EXCEL file (I call it MASS) and place all the calculated values in that new file. In that new file, the titles have to be on Column A and the calculated values on Column B. For example, File "AAA" has 120 calculated values. In MASS, the word "AAA" has to appear on Column A a number of 120 times and the 120 calculated values are then placed on Column B. Each value must correspond to the word "AAA". The same procedure goes for "BBB", in which the word "BBB" is placed on Column A after the end of "AAA". What I want to achieve is by using a macro that will look up the title of the Active Worksheet and then scan Column A in MASS. If the title matches whatever is in Column A, then the macro will paste the corresponding calculated values onto Column B automatically. For example, for file "GGG", the macro will look up the word "GGG", scan through Column A in MASS for the start of the word "GGG" and paste the calculated values at the start of Column B. Could I have an idea of how might the macro looks like?
View Replies!
View Related
Running A Macro When A Certain Value Is Found Anywhere In A Certain Column
I’m working in Excel ’03 with a large table. I’m hoping to run a macro against a cell with value ‘X’ whenever that value is found within a particular column. I’d like this to be relative and not tied to a particular column so I can tweak it as needed down the road and run it against different columns. In the example below, lets say I’m working with column B. I’d like something that would go through every cell in the column and run a macro when it comes across a particular value. Let’s say this value is x. __A B C D E F 1 a x a a a x 2 b a b b b a 3 c x c c c c 4 f f f x f f 5 s s s a s s 6 g x g g g x 7 a a a a a a 8 s s s s s s So, in my example, the macro would be run on B1, B3, and B6. And, if I wanted to go back in later, I could run this on column D and the macro would run on cell D4.
View Replies!
View Related
Use A Column Of Data If A Specific Value Is Found
I have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell. Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19. fig 1 Coloms A B C D Row A 5 5 6 6 Row B 5 6 6 8 Row C 4 5 6 7 Row D 5 4 4 5 Total 19 20 23 26 fig 2 Coloms A B C D Row A 1:00 2:00 3:11 4:01 Row B Row C Row E Row F 1:19 2:20 3:33 4:27 Row F-A= 19 20 23 26
View Replies!
View Related
If Text Is Found On Two Sheets, Copy Entire Row
I have one master worksheet with 1000 ID's. I also have 10 other spreadsheets with 1000 ID's. I need my macro to use the master worksheet and identify where the IDs are located among the 10 spreadsheets. If there is a match, then my macro should copy the entire row and place it in an output file (another spreadsheet). The following code only tests the master worksheet and three other sheets (one being null/error is ID not found). Sub Macro5() ' ' Macro5 Macro Dim Raw, Mix1, Mix2, error As Excel.Worksheet Set Raw = Sheets("RAW_DATA") Set Mix1 = Sheets("Mix1") Set Mix2 = Sheets("Mix2") Set error = Sheets("error") Raw.Select For x = 1 To 1000 For y = 1 To 1000 If Raw.Cells(x, 1) = Mix1.Cells(y, 1) Then.................
View Replies!
View Related
Handle Find Macro Error When Text Is Not Found
I have the following macro which I've pieced together which works great at clearing the cell as long as the text entered by the user is found in the specified column. Unfortunately I'm a big rookie with this stuff and I can't figure out how to handle the process/error when an entered value is not found. When ChosenRow returns with a 0 I get a Run time error '91', Object Variable or With Block variable not set. The code... Private Sub CommandButton1_Click() oldSheet$ = ActiveSheet.Name Dim ChosenRow As Long Dim loc As Variant Application. ScreenUpdating = False With UserForm4 loc = .TextBox1.Text End With With ActiveWorkbook Sheets("Reference").Select End With
View Replies!
View Related
Calculate If Specific Word Found In Column
I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula = SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word. Range("B7:B257").Find(What:="Reservoir").Activate
View Replies!
View Related
Search Column Delete Row If Value Found
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
View Replies!
View Related
Search 1 Column All Worksheets & Go To Found Values
I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that. Sub search_box() Range("B49000").Select Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End Sub
View Replies!
View Related
How To See Where The 55th Place In A Cell Is
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap. I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell. I have thought about coloring the first 55 character/spaces ? or inserting a character at space 56 ? Example below XXXX XXX XXX XX XXXXX XXX XX XXXX XXXX XXXX XXX XXX XXX XX~XXX X XXXX XX
View Replies!
View Related
Search Column Bottom Up Then Delete Rows Based On Value Found
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change. I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell. For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
View Replies!
View Related
Lookup Column Values In Another & Return Value If Match Found
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1". The problem I've run in to is that both the lists change in length each time I have to run this (so no set range). I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow. Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View Replies!
View Related
Pull Found Records From Table Based On Single Column
I have the following data :- SHEET 1 COL A_______COL B________COL C V1990_______J100_________U1212 H2323_______Y999 U2222 SHEET 2 COL A_______COL B________COL C U2222 I0000_______U8900________T67888 H2323 I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
VB Code To Place Formula In Cell
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible) ,,,,,,,,,,,,,,, Place formula in Active Cell Row & Column S =MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m" I have tried several combinations but with no success. Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
View Replies!
View Related
Place Date In Cell H6 Automatically
I have the following Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub I just want the date to appear in cell H6 autmatically and the time to appear in H7.
View Replies!
View Related
|
TRACKER
