Macro - Select Multiple Cells By Searching For Specific Text
Nov 8, 2013
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
If I'm using this function to find the specific text "EXPIRED" in the specified cells, how can I add additional cell ranges on the same sheet to this function?
The easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code: Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx" End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.
Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in $A:$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.
This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)
Here are screenshots of what I am trying to accomplish. [URL] .......
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.
I have the following code, that searches the entire workbook. I came across an issue when searching for terms that are on my main page where my search results appear.
Code: Public Sub Find_box() Dim ws As Worksheet, Found As Range, rngNm As String Dim myText As String, FirstAddress As String, thisLoc As String Dim AddressStr As String, foundNum As Integer
1) automatically check the active row to see if it contains specific text (i dont' care if there is other stuff in the cell or cells), such as "truck 1 blahblahblah" and checks for specific font color, such as green.
2) if the row does, then I want it to copy ALL the cells in the active row EXCEPT Column A (which contains the date), and then paste them twice, one 21 rows down from the active row, and one 35 rows down from the active row.
3) Once copied and pasted, modify the pasted cells slightly. The first pasted cells need to say Truck 2 blahblahblah in Column C and be in blue font (instead of the original Truck 1 blahblahblah in Green Font), the second needs to say Truck 3 blahblahblah and be in yellow font. Everything else that was pasted will be the same EXCEPT they will be in blue or yellow font.
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro.
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
For instance:
PhraseHidden word Please search the text for the hidden message: Blue The hidden word, Green, may be at any point And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
Im sure this has been addressed before but i need a macro that would act like a "Contains" function. I have Column B that has user comments. These are sentences like "I have a billing issue" or "My item is damaged". My goal search these for keywords and then have a value (category) populate in Column C
Heres an example. the Category is Damaged Goods: Keywords associated with it are "broke" "broken" "replacement" "damaged" "not working"
Another category is Billing: keywords include "bill" "credit" "account"
I need a macro that will search all the comments and if "broken" (for example) is found anywhere within the comment, the value "damaged goods" will be populated in the same row, column C
I have a column that is filled with text of varying lengths and I'd like to search through each cell in that column looking for a specific word or words. Unfortunately the length of the text varys greatly between each cell and there is very little uniformity so I need to be able to search through the entire entry in each cell and then highlight that row if a specific word or words are found.
I'd also like to be able to add a number "1" in another column on the same row if the search finds a word or words. Any help would be greatly appreciated.
If the cell only contains the exact word or string I'm looking for then it's easy but I can't figure out how to search through text in a cell that contains more than I'm looking for.
example: Lets say I'm loooking for "caught fire" column Q contains: 1 "The computer caught fire after several hours" 2 "A house on the hill caught fire"
If "caught fire" exists in the cell being checked, then highlight the row and put a 1 in a specific column, lets say J.
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date Column D = loan# A Column E = loan# B Column F = status Column H = followup needed (Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows: Column G&H = Merged cells where due date will need to go Column I&J = Merged cells where loan# A will need to go Column K&L = Merged cells where loan# B will need to go Column M thru S = Merged cells where followup needed will need to go
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code: Sub CheckIfCharacterIncluded() For Each MyCell In Selection If InStr(MyCell.Formula, "#") Then MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#")) End If Next End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
The code I currently have allows me to search for one text entry in the column and then copy and paste all those entries into the next worksheet. There are 5 different text entries I'd like it to look for but can't figure out how to do it. I have a work around by using wildcards, but then I have to write another macro to delete out the ones I don't want.
Where is says "DNA - weapons", I'd also like it to allow for "DNA - paternity" and a couple other options.
Here is my current code:
Sub Copy_To_Another_Sheet_1() Dim FirstAddress As String Dim myArr As Variant Dim Rng As Range Dim Rcount As Long Dim I As Long Dim LastCol As String Application.ScreenUpdating = False
I have an Excel 2003 spreadsheet, and in certain cells, I have a single letter. (e.g.) In A1, I have the letter A, in B1, I have the letter B etc.
I am trying to write a macro which will allow me to select a specific row, and depending on which letter is in which cell, set the cell directly below it to the numeric equivalent of the letter (e.g.) if in cell A1, there's the letter A, I want the number 1 in cell A2; and if in cell E5, there's the letter Z, I want the number 26 in cell E6 etc.
All I've got, is a VERY long macro, which will take row 1, and change each cell value to it's numerical equivalent. Here's an extract:
For Each cell In ActiveSheet.Range("A1:Z1") If (cell.Value = "A") Then cell.Value = "1" End If..................
I have a workbook with a macro that searches through worksheets to find certain values and place the location of the value in a worksheet cell. The following line ontains the found cell.
I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
i have a list of about 20,000 phone numbers that I need to edit.
all start 01 123456 01 123457 01 123458 etc
I am trying to create a macro that will change them to
+001123456 +001123457 +001123458 Etc
However, no matter what I try it always comes out as
+001123456 +001123456 +001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost. I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
I am trying to select specific cells and transfer this data into a new row in a different worksheet. The code i am using i used sucessfully on a user form to take the entries from text boxes and place in a row in a new worksheet. I would like to try and do the same thing but on a worksheet rather than a user form. My code is
How do you select specific coloured cells from a range of data in one go, without having to scroll through the worksheet and pick them out individually?
I'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.
After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.
