I have an email list that I email. Sometimes I get some that doesnt exist anymore. So my program sends email out and respnods to me that says these emails do not exist and gives me the emails. So I manually have to go and find them one at a time on my list to delete by CTRL F. Is there a way to submit all email names to find at once instead of doing one at a time.
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.
How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher. I have a group of 50+ workbooks which are all set up in the following format:
-Contains 30+ sheets. -Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible. -However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.
Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.
I have two sheets,one called pattern of about 25000 rows and one called rp1, which is a fresh sheet downloaded each night from the web. What I want to do is select the names in column 3 of rp1 and printout in the same or a new sheet every row containing this name from the pattern spreadsheet.So for every horse named in col c,I want its complete record,could be 1 row or maybe up to 20 rows output,from the pattern worksheet(for each). They will all be underneath each other. I simply want excel to extract all the data belong to these names in rp 1 from the pattern large sheet. The names are in col r of the pattern sheet.
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)
I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.
I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.
So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.
I want to be able to have a set of names replaced by one singular name, e.g.
Hilton monkeys hilton monkeys on top Hilton Power Hilton jimmy Hilton jimmy on top Hilton jimmy on top with bananas Marriott jimmy Marriott jimmy on top
(ofcourse these are made up!). What I would like is to group the names to simply 'Hilton' and 'Marriott'. There must be a way of doing this, so I don't have to select all the separate cell numbers. Is there a simple way. I have tried using replace in Excel and that doesnt allow me to do it. Macros must do, but I'm trying to avoid having to write 5000 scripts selecting each particular cell. Is there any way of doing this?
Surely the 'replace' function should do it with some key character like replace hilton * with Hilton??? Thats all I want, but there doesnt appear to be that option.
I'm trying to pull 20 random names from a list of them every time I open the spreadsheet, and have them copied onto the clipboard to be pasted wherever. I have some code I found to accomplish this, but I'm not exactly what you would call VBA savvy. Could anyone look at this and see what I'm doing wrong? Or if it is right what I'm doing wrong to get it to work?
Sub GetRandom() Dim iRows As Integer Dim iCols As Integer Dim iBegRow As Integer Dim iBegCol As Integer Dim J As Integer Dim sCells As String
I need to use the VLOOKUP feature from the most latest files, I am using VLOOKUP in VBA code which is working fine, however the problem is that the files from where I do VLOOKUP are added randomly in a week.
There are 2 Files, 1st File where the data would be looked up from the 2nd File, Now the 2nd File has names like this "Offline Data as on 10th May 14", "Offline Data as on 13th May 14", "Offline Data as on 23rd May 14", etc.
Now these files are send by another department, and each time the most recent data has to be searched from these files.. All of these files have to be maintained separately for record purposes.
Hence in the VLOOKUP, Lookup file keeps on changing...so is it possible to make a code in such a way that the code picks up the most recent file for reference, from the above example we would need to pull the data from the most latest file which is "Offline Data as on 23rd May 14" , if any other file is added in that folder more recent than the one mentioned than the code should automatically link it to that file.
I have managed to codify the lookup but due to this filenames being changed always I have to do this manually..as this has to be done at several columns in several sheets its extremely time-consuming.
So, logic code to pick the most recent date which is embedded in the filename...
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers() Dim managers1 As Range Dim names1 As Range Dim n1 As Variant Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
I have a spreadsheet with two columns - one with names, the other with values. In some cases, the values column alongside a name is blank. Is there a formula-driven method (not a pivot table) that could produce a separate list of only the names that have a value in the value column? The formula needs to be dynamic, not produced via a filter.
I have a table in the format below. Pin_11 in this table is CLK and Pin_12 is STRB. I want to replace Pin_11 with PIN_CLK. Basically I want to look for CLK in Column3 and record what ever is in Column1 and Column2 on the same row (base row). For every repetion of that value in column1 and column2, in the rows above and below the base row, I want to replace column 2 with PIN_<column3 value>.
See below for initial table in column 1,2,3 and final result in column 6,7,8. I have a huge file this format of data that I need to end and was wondering if there is a script or formula to do it.
The following code works to update the target sheet based on matching column names within the source sheet.
However, I would like to bolster this by not only updating based on matching field names, but also the primary key in column A. Meaning the data from the source sheet isn't always in the same order (based on primary keys) as the data in the target sheet. So I want to keep the order of the primary key in the first column and then update matching field names in column B through the last column where the primary keys match between the source and target worksheets.
Sub CopyDataBlocks() 'VARIABLE NAME 'DEFINITION Dim SourceSheet As Worksheet 'The data to be copied is here
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
I am needing to do a find and replace on multiple values in an excel worksheet. For example in column d when you find ABC123456 replace with XYZ654321. But there are several hundred in a sequence that need to be replace w/ a different sequence.
Is there something I can do in vb that would help me do this? For example the argument is:
I need to recode 30+ files the have the old account numbers and I need to replace them with new codes ie
Old New 123456 10_1240 256789 12_1130 789123 15_1122
I have an excel spreadsheet which contains some 800 accounts in the format above showing old account number in column A and the new account number in column B. How would I write the VBA code to search all files in a specified folder search all rows in column E and find/replace using my spreadsheet containing the mapping details as above? or in the very least just be able to run a macro to replace all the old codes in an individual spreadsheet.
I want to have a macro in excel where it will do a simple find and replace, except I want it to search for more than one word and replace it with one eg, I want it to look for any month and replace it with january.