Over 1 million tech questions and answers.

Solved: Highlight Multiple Values with an Excel Macro

Q: Solved: Highlight Multiple Values with an Excel Macro

I want to Highlight Multiple Values with an Excel Macro. I want the macro to loop though and grab the list/range of values from another sheet and use it in the Array.

I want to avoid having to enter such a big list of values for the Array. I want to use something like "SearchVal() = Range("Lists!A2:A62").Value" but I get an "out of range" error. Here is my code now.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub Highlight()
Dim SearchVal() As Variant
SearchVal() = Array("ALM", "AXV", "AZZ", "BCT", "BHN", "CRE", "CSI", "CVM", "DCH", "DEK", "DER", "ESE", "GCV", "HCO", "HND", "HSI",
"IBO", "ILW", "KCM", "LEO", "LES", "LPA", "MCY", "MEX", "MHS", "MRM", "MSL", "NCL", "NTX", "NZI", "PHC", "PLA", "PPH", "PSE", "PUV",
"PVN", "PWL", "RKC", "RSN", "SCM", "SEL", "SGA", "SHC", "SRG", "SVL", "SYV", "TGC", "THA", "TSL", "VAV", "VID", "VTA", "WHE", "WHH",
"WIH", "WWJ", "XUU", "YKC", "YLI", "YVE", "ZWO", "VSD26", "HSI")
Application.ReplaceFormat.Interior.ColorIndex = 6
Application.ReplaceFormat.Interior.Pattern = xlSolid
For i = 0 To UBound(SearchVal)
Sheets("Sheet1").Cells.Replace What:=SearchVal(i), Replacement:=SearchVal(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

Next i
Application.ReplaceFormat.Interior.ColorIndex = xlNone
End Sub

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Any help is appreciated

Linda

RELEVANCY SCORE 200
Preferred Solution: Solved: Highlight Multiple Values with an Excel Macro

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Highlight Multiple Values with an Excel Macro

Read other 8 answers
RELEVANCY SCORE 143.2

Hi,
This is probably a very simple macro but programming isn't my strong point.
I need an Excel Macro that will be able to scan the first column of a worksheet row by row for certain document numbers i.e. "09-005", "09-1052", "586463" and highlight them. There is a substantial list of these.
I managed to figure out that I need a range and how to highlight, but am getting stuck on how to find the multiple document number values.
Any and all help is greatly appreciated
Thanks in advance
 

A:Solved: Highlight Multiple Values Excel Macro?

Read other 8 answers
RELEVANCY SCORE 96

Hello,
I've searched and have not been successful to find an answer to my issue.
I have a workbook with multiple spreadsheets. Each spreadsheet has a list of employess with tasks and hours.
I need a macro that will search for the employee name and add the hours spent working on a task.
I also need to search for the different tasks and add the total hours spent on the specific task.
Workbook contains multiple tabs (Summary, week1, week2, week3, etc)
Each worksheet has three columns and employee name may be in the first column multiple times with different tasks:
Employee Tasks Hours
Jason Dyer dishes 8
Joyce Brown lawn 12
Donald Steiner vacuum 16
Jacqueline Lowe dusting 4
Robert Jones mopping 2
Barbara Fritts trash 8
Lisa Stillman painting 10
Antionette Adkisson repair 6
Billy Barkley errands 14
Ian Grayson sweeping 18

And the Summary has:
Employee Total Hours
Jason Dyer
Joyce Brown
Donald Steiner
Jacqueline Lowe
Robert Jones
Barbara Fritts
Lisa Stillman
Antionette Adkisson
Billy Barkley
Ian Grayson
Task Total Hours
dishes
lawn
vacuum
dusting
mopping
trash
painting
repair
errands
sweeping

Any help will be appreciated!
 

A:Excel 2007 search and match macro, add values in multiple spreadsheets

Read other 7 answers
RELEVANCY SCORE 92

I know this is probably very simple, and I am going to hit myself once the answer is found, but I cannot seem to think of the solution for the question below.

I need to highlight all the cells in a column that does not contain a zero. How would I go about doing this with code?
 

A:Solved: Excel Macro - Highlight Certain Cells

Read other 9 answers
RELEVANCY SCORE 87.2

Hi

I'm trying to make a macro that match table in sheet "Vocabulary" to the second table (columns Q,R,S,T) in sheet "Overview" and compute correct values in column U using weights from column F, sheet Vocabulary on values from a corresponding cell at column M, sheet Overview. The entries for every ISIN in the sheet Vocabulary are in no particular order and some positions from sheet Overview don't have to exist in Vocabulary at all (but I still need them to display in the table on the right to be able to make charts from summing these categories).

How should the end result look like is displayed on the last sheet.

Can anyone help?
Thanks

Michael
 

A:Solved: Excel: Macro to match 2 tables and compute values

Read other 7 answers
RELEVANCY SCORE 86.4

Hello Everyone,

First time user of the forum here and it does seem everyone is very helpful! I did a search and could not find search macro for a list of values so I'm adding a new post. My apologies if this was answered before but hopefully someone can point me in the right direction.

Here's what I have:

I have 3 worksheets:
1. List of Search Criteria (List of 100 or so countries in column A)
2. Huge Data Set (Sales Data; column K, L, or M will contain country name)
3. Blank Output sheet

My goal is create a search button that will look in the Huge Data Set for sales transactions occuring in the list of countries specified on Sheet #1. The country info could be in any of the 3 rows (K, L, M) on Sheet #2. I would then like any row on the Huge Data Set with a matching country to be copied to the 3rd worksheet.

I hope my explanation makes sense.

Any help?

Simon
 

A:Solved: Excel Macro - Search List of Values, Copy, and Paste

Read other 13 answers
RELEVANCY SCORE 85.2

Hi all,

I'm having trouble writing a macro that'll help me automate this process. In the attached, I have a list of countries in the Countries tab, and 10 line items in the Data Set tab. I want to look for each of the countries in columns K to M in the Data Set tab, and for each line that hits, I want the macro to copy that line and paste it in the Output Sheet tab. Then the macro should go down the list of countries and repeat this process until it finishes looking for the last country in the Countries tab.

I'm thinking some kind of For loop is required, but any help would be greatly appreciated! Thanks!
 

A:Solved: Urgent: Excel Macro - Search List of Values, Copy, and Paste

Read other 9 answers
RELEVANCY SCORE 84.8

Hello Everyone,
(This is kind of long but I didn't know how else to explain everything)

There are two parts to my question below.

I have a spreadsheet with roughly 40 columns

An Example of some of the columns would be:

Code:

B1 = Client Name E1 = Travel F1 = Backup G1 = PC Install H1 = Data Recovery | X1 = Total | AE1 = Summary
B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)
B3 = Tom Daniel E3 = $70.00 F3 = $0.00 G3 = $0.00 H3 = $725.00 | X3 = $795.00 | AE3 = (See Below)
B4 = Rene Little E4 = $70.00 F4 = $125.00 G4 = $95.00 H4 = $995.00 | X4 = $1285.00 | AE4 = (See Below)

Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

I know how to list items in 1 cell using the c... Read more

A:Excel: Checking values on multiple cells and transferring values to 1 cell

Read other 16 answers
RELEVANCY SCORE 84.8

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will b... Read more

A:Excel: Checking values in multiple cells and transferring values to another worksheet

Read other 16 answers
RELEVANCY SCORE 84.8

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will be ... Read more

Read other answers
RELEVANCY SCORE 83.2

Hello all. I have 2 spreadsheets which contain subscription information for 2 different products costing £21.58 and £5.96. I'm trying to merge this information so that if Mr A in spreadsheet 1 is shown as subscribing to Product B and the same person in spreadsheet 2 is shown as subscribing to Product C, an additional column shows in spreadsheet 1 so that it then appears as: Mr A - £21.58 - £5.96

If not, the columns would show Mr A - £21.58 - 0 (or blank) etc

The code for VLOOKUP is straight-forward but only returns the first row. I need it to reference every row and cross-reference the data.

I'm using Windows 7 and Excel 2010. I've attached a small example though the actual spreadsheet has around 3000 rows. I only need to do this once to get a single file up and running.

Any ideas

Many thanks

Nos
 

A:Solved: Comparing multiple values in excel

Sorry all. Now solved using a simple VLOOKUP and defined names with a fill-down. Doh!
 

Read other 1 answers
RELEVANCY SCORE 82.4

Hey guys and gals,

Here's what I'm trying to do: I have an Excel spread sheet with 3 columns and many rows depending on the data. I need to be able to search the data in column C for about 10 or so words and highlight them. The problem is, the words in column C have been exported by a program so each word ends with a comma then the next word begins with no space so it's difficult to search. Example, Text1,Text2,Text3 and so on. Is there a macro or software that I can use that will search all the rows for column C and highlight the group of words I choose? I'm using Excel 2007.

Thanks for your help!
 

A:Excel Macro to highlight words?

Read other 7 answers
RELEVANCY SCORE 82

Thanks to everyone who helped me with that last one.

What I'm stuck on now is an array that brings up all of he rows that contain a certain policy number in column A.

So Say We have;

TL123 X B A 3C
TL456 C B E 4D
TL586 X B A D4
TL456 C B A 3C
TL892 C A E 4F
TL586 C A E 2B

And someone here say put the value TL586 in cell m6 they would get an answer of;

TL586 X B A D4
TL586 C A E 2B

The current sheet is approximately 500 rows by 30 columns and will be growing daily, so running this through IF Calculations is taking about 2 minutes to recalculate each time, so that isn't viable at the moment.

Any help appreciated,
 

A:Solved: Excel - How to return a multiple of matching values?

Read other 6 answers
RELEVANCY SCORE 81.2

OK... Here's something I have been looking at for a while and getting myself all in a tizz...!!!

I have 2 worksheets within 1 workbook - see attached example. The first contains unique references (ABx in Column A) with associated processes in Column B. Each cell may contain more than one process - comma seperated. The second sheet contains unique references (XYx in Column A) which map back to ABx references in Column B (again, each cell can contain multiple mapping references - comma seperated).

e.g.
Ref - Process
AB1 - Create, Update
AB2 - Create, Read
AB3 - Delete, Update

Ref - Maps-to
XY1 - AB1, AB2
XY2 - AB1
XY3 - AB2

Now, what I am looking to do is within sheet 2 (containing the XY references mapped to AB references) is create a column which will display the corresponding processes without duplication.

e.g.
Ref - Maps-to - Process
XY1 - AB1, AB2 - Create, Update, Read
*Note that AB1 and AB2 both contain Create which should only be returned once.

I have tried using =VLOOKUP(B2,'Reference Sheet'!A:B,2) but this only uses the first reference to return results and therefore using the above as an example the result would not include "Read".

Any takers? Hope someone can help...

Thanks in advance!
 

A:Solved: Excel: Cross Reference multiple values in cell

Read other 16 answers
RELEVANCY SCORE 81.2

Hi There

I have run into an issue with excel that's a little beyond my amateur coding skills

I think this can be solved with some simple vbscript or nested excel formula

All i need to happen is to compare a list of names and one additional value to another list of names and add the missing value. Easier if i give an example of data

the columns are as follows

Column A Column B Column C Column D
Name Value Name Value
Bob, Test Full Bart, Test
Bill, Test Part Bob, Test
Ren, Test Non Bill, Test
Bart, Test Part Ren, Test

So in column A and B is the reference data, and you see Column C and D is where the data needs to be matched / copied to. The script / formula needs to in this case work its way down column C - look up the matching value in column A and then copy whats in column B next to the matching value to column D.

Example is with the above data it would first hit in column C - Bart, Test - it would then lookup cell A for Bart, Test - and copy whats in column B beside it which is "Part" to blank cell in column D

Then it would move on to Bob, Test in column C - it would then lookup cell A for Bob, Test - and copy whats in column B beside it which is "Full" to blank cell in column D

I have a massive list of 3800 names that gets emailed to in a jumbled order and may grow in cell numbers as well so i cant count on a simple A to Z sort to help out - with additional information that i need to marry up to my existing data. I need an easy ... Read more

A:Solved: Basic Excel Question - Comparing multiple values

This is what I think you need (See attached file) Look at the formulas in column D. The first formula can be copied down that column as many times as you require.

HTH
 

Read other 3 answers
RELEVANCY SCORE 79.6

G'day,

I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received $100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent $50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!
 

A:Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.

lol
Hew
 

Read other 3 answers
RELEVANCY SCORE 78.8

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

A:Excel macro: Return values from 2 cells on same row

Read other 9 answers
RELEVANCY SCORE 78.4

Hi,

I searched the forum but could not find what I am looking for.

Selection for row that contains either apple or banana
I have an excel (2007) with around 45k rows. I need to highlight entire rows based on the value in 'Item' column. If the row has either 'apple' or 'banana' in column A, the entire row should get highlighted.

Now i did try the conditional formatting -> new rule -> use a formula to determine which cells to format and it worked fine for a single value defined. However i want to input more than one value and all the rows with either of the values should get highlighted. is it possible? I have attached a sample file here.
Selection for row which contains 2014
Secondly, if the row has 2014 in one of the colums, say D (like in the sample sheet), the row should get highlighted. I am not using it with the above formatting i..e with apple/banana highlighting. This is for a separate task.

however i made both table in a single file (which is attached).
 

A:Solved: Highlight entire row based on 2 values

Read other 11 answers
RELEVANCY SCORE 78

Can anybody help me with this? I have data in cell A1 I want A2 to have a different piece of text inside it depending on the range of A1.

For example:
if A1 was between 0-20 A2 should say Low
If A1 was between 21-50 A2 should say Medium
etc

Is there an easy way to do this equation?

Thank you for any help.
 

A:Excel formular or macro to chnage text between values

=if($a1<=20,"Low",if($a1<=50,"Medium",""))

This formula assumes that A1 is always greater than 0, otherwise it gets more complicated, let me know if thats the case though. Also, inside the "", you can place text if none of the conditions are met.

As you can see it can get a little messy to read, so you could name the table column and reference the column in the formula or if its not part of a table name the particular cell.

In Excel 2007, it's easier to refer to columns using [] without the need to name them (this is a lot easier to maintain and read should you need to change column references in the future).
 

Read other 1 answers
RELEVANCY SCORE 77.2

I'm familiar with Conditional Formatting in Access reports but can't seem to get this to work. I have a report that returns names, addresses, company names, phones, etc. All I want to do is have it highlight the ones that are null. For example, if a record is missing the phone number, I would like for that phone number field to be yellow.

Seems like it should be "Expression is Null" but it doesn't work.

Thanks!
Diane
 

A:Solved: MS Access report - Can you highlight Null values?

Read other 11 answers
RELEVANCY SCORE 77.2

i am uploading excel work book with 4 sheets, sheet no1 contains the record to be verifed/matched with the records in sheet no.2. we will match BTC_Name, BTC_Fname, Deg_RegNO of sheet1 with student name, father name, reg.no. of sheet no.2, if records of sheet no.1 are matched/presented in sheet no.2 then the whole row of sheet no.1 should be copied in to sheet no.3 (if matched display here) else other wise mismatched/ not presented records of sheet no.1 in sheet2 should be displayed (whole row) in sheet no.4 (not matched display here). i have shown sample values in sheet 3 and in sheet 4 taken from sheet 1.
thanx.
 

A:compare/match multiple column values in multiple excel sheet

Read other 11 answers
RELEVANCY SCORE 76.8

Does any one know if you can take a single line of code in a macro....
Code:

"=OR(D12=""1,1,1,2-Tetrachloroethane"",D12=""1,1,1-Trichloroethane"",D12=""1,1,2,2-Tetrachloroethane"",D12=""1,1,2-Trichloroethane"",D12=""1,1-Dichloroethane"",D12=""1,1-Dichloroethene"",D12=""1,2-Dichlorobenzene"",D12=""1,2-Dichloroethane"",""D12=1,2-Dichloropropane"",AND(P4=""ETECHRYOBIAND-W""))"

Make multiple lines out of the code so it is easier to read?
 

A:Solved: Excel Macro Multiple Lines

use underscore character with one space and the apersand character...you'll have to add quotes at the begining of the line...
With ActiveCell.Formula = "=OR(D12=""1,1,1,2-Tetrachloroethane"",D12=""1,1,1-Trichloroethane""," & _
"D12=""1,1,2,2-Tetrachloroethane"",D12=""1,1,2-Trichloroethane"",D12=""1,1-Dichloroethane""," & _
"D12=""1,1-Dichloroethene"",D12=""1,2-Dichlorobenzene"",D12=""1,2-Dichloroethane"",""" & _
"D12=1,2-Dichloropropane"",AND(P4=""ETECHRYOBIAND-W""))"
 

Read other 2 answers
RELEVANCY SCORE 76

I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.
 

A:Solved: Excel Macro -> Delete Multiple Rows

Read other 16 answers
RELEVANCY SCORE 75.2

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

A:Solved: Sort Macro for multiple files- Excel 2010

Read other 6 answers
RELEVANCY SCORE 75.2

HI,

I have a huge product listing that i need to find and replace on a mass scale. I have one spread sheet that has 3 columns (see example below)
Column A****************Column B**********************Column C
1************************ M2123*************************M3455
2************************M3455*************************M3455
3************************M3433*************************M3455
4************************M6543*************************M3433
5************************M8793*************************M3433
6************************M6543*************************M7832
7************************M7832*************************M7832
8************************M4893*************************M7832
9************************M3938*************************M2123
10**********************M4837*************************M2123
*********************************************************M2123
*********************************************************M2123
*********************************************************M4837

In essence Column A is the id for column B (A=B), column C is longer than column a and b, because it contains duplicate (M)numbers. I would like to use the defined id in column a to replace the (M)numbers in columns c

So the spread sheet with the end result would look like this

Column A****************Column B**********************Column C
1************************ M2123*************************2
2************************M3455*************************2
3************************M3... Read more

A:Solved: Macro Multiple Find and Replace in Excel 2010

Read other 11 answers
RELEVANCY SCORE 75.2

Hi,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. I want to be able to find the value from Column A and replace it with the value in the same row in column B wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).
Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,

Thanks

Lynchie
 

A:Solved: Macro Multiple Find and Replace in Excel 2007

Read other 9 answers
RELEVANCY SCORE 74.8

In sheet 1 Column A has current values, Column B has the new value for each entry.

Column A Column B
Old value New Value

Document 1 SOP-001
Document 2 SOP-002
Document 3 SOP-003

In sheet 3 has a list of entries
Column A

Document 1
Document 3
Document 3
Document 2
Document 1

What formula can I use to automatically replace the value SHEET3:ColumnA with the appropriate value from SHEET1:column B? The lists are quite long. Resulting in a list on sheet 3 that looks like this:
SOP-001
SOP-003
SOP-003
SOP-002
SOP-001
 

A:Solved: Excel-Substitute values from a list of new values

Hi bongiojf, welcome to TSG.

You can do this with a vlookup formula, assuming that the old values are in the leftmost column and that they are sorted alphabetically.

On sheet 3, in column B (or any blank column) use this formula for the first cell and then copy it down the rest of the column:

=VLOOKUP(A1,Sheet1!$A$1:$B$3,2)

Change the range $A$1:$B$3 to be the range of data you have on Sheet 1. The number 2 at the end of the formula indicates that the new values are in the second column from the left. If they are not, change the number accordingly. For example, if they are in column D, the value would be 4.

Once you have the new data in your blank column, you can copy the column and paste special/values over the existing data on sheet 3.

Hope that helps.
 

Read other 1 answers
RELEVANCY SCORE 74.8

Hi,
I have frequently used an array formula like this to look up multiple records in a list in Excel:
{=INDEX($A$1:$B$7),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)}
It nicely retrieves all the rows where A10 is the value to be looked up.

However I have not had any success using the same type of formula with an OR statement:
{=INDEX($A$1:$B$7),SMALL(IF(OR($A$1:$A$7=$A$10,$A$1:$A$7=$A$11)),ROW($A$1:$A$7),ROW(1:1)),2)}

I want to retrieve all the records in a list where the value is what is as in A10 OR A11.

Does anyone know how I can achieve this?
Thanks!!!!!
Janie
 

A:Excel - looking up multiple records of multiple values

Read other 7 answers
RELEVANCY SCORE 74.4

I need to delete multiple columns within an Excel worksheet based on the headers in the first row. What would be the best way to perform this operation?

I have been playing around with the code below, but I feel as though there could be a better way.

Sub test()

Dim myArr As Variant

myArr = Array("Test1", "Test2", "Test3")
If Range("C1").Value = myArr Then
Columns("C").Delete shift:=xlToLeft
ElseIf Range("D1").Value = myArr Then
Columns("D").Delete shift:=xlToLeft
End If

End Sub
 

A:Solved: Excel Macro -> Delete Multiple Columns Based on Criteria

Read other 16 answers
RELEVANCY SCORE 73.6

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.
 

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If

?

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Selection.EntireRow.Delete
Application.EnableEvents = True
 

Read other 2 answers
RELEVANCY SCORE 72.8

Hi,

I need the help of a guru. I'm an intermediate (NOT ADVANCED) user tasked with an assignment and I'm not sure of the best way to execute the idea. I have two spreadsheets FILLED with data. I have to take Manufacturer numbers from a column in one spreadsheet, and compare them to the the manufacturer numbers in a similar column of another spreadsheet to find matches. There are thousands of numbers! What is the best and quickest way to do this?! I would love to open List B and run a check for all the numbers from List A at the same time. Hope I'm making sense because I need a savior.

Please answer my question (in a user-friendly, step by step fashion if you would) and consider your good deed for the day done!

Thanks,

*Overwhelmed*
 

A:Excel - Search Multiple Values at Once (Overwhelmed)

Welcome to TSG dusk.

Look at the Vlookup function.
 

Read other 2 answers
RELEVANCY SCORE 72.8

Hello.
I have a document with 4 columns, in column one there are codes, in column two there are names and in columns 3 and 4 are start and end dates.
There can be multiple codes for each name.
What I'd like to do is create a second worksheet with the names listed (once each) in column A, and have the corresponding codes and dates in the adjeacent cells, so I'd go from this:

Code____Name____Date1____Date 2
A1______ABC123___01/01/05__20/01/05
A2______ABC123___15/01/05__12/02/05
A1______DEF456___01/01/05__08/01/05
A5______DEF456___07/02/05__10/02/05
To this:

Name_____Code1___Date1____Date2____Code2___Date1_____Date2
ABC123_____A1_____01/01/05___20/01/05_____A2____15/01/05___12/02/05
DEF456_____A1_____01/01/05___08/01/05_____A5____07/02/05___10/02/05
A name may have between 0 and possibly 10 codes.
Is there a way to do this?
I've looked at VLOOKUPs, Pivot tables and Row Indexes, but can't seem to get it to work.

Any help is appreciated.

Gram
 

A:Excel 2002: Lookup multiple values

i think a pivot may do it but not maybe give you the order you have more a grouping
 

Read other 2 answers
RELEVANCY SCORE 72

....A................. ........B ..........................C

1. AMDRG.SA ........AMDRG.CCM.......CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA';

I am not sure how good that is going to look once i post this, but here is a rundown of what it is.

Lets say those are 3 columns, A, B, and C. I would like to have a macro or script that will take what is in column B, which is also inside the text of column C, and change the text AMDRG.CCM to what is in column A(AMDRG.SA). So when i am done, the info in column C will look like this...
CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA'
Column A and column B will always line up with eachother, but column C will not always line up right beside the cells associated with what the replacement text is.

Thanks for any help that you can give!
 

A:Multiple find and replace cell values in excel

Can you post a sample workbook with a few more records?

Rollin
 

Read other 3 answers
RELEVANCY SCORE 71.6

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

Read other 1 answers
RELEVANCY SCORE 70.4

Hi all,

This may be more complex than I think but I have searched hundreds of forum posts all over the place and while I've come close to finding a solution to this; nothing has quite described a way to do this...

Basically, I have a roster for staff (attached is a simple sample I've thrown together to show you what's on the rows / headers & sample contents...obviously the actual spreadsheet is much bigger and month-to-view on each sheet).

Let's assume the following:
- sheet 1 and sheet 2 have staff rosters on as per the example spreadsheet
- sheet 3 is where I want to display the search results
- the same name may/will be present on different dates and different shifts
- this is for me and not an 'end user' so it doesn't have to be in any way flash in its working or pretty!!!

I would like a search entry box on sheet 3 (let's say in cell A1).

I need the following results returned for EACH occurrence of the searched name (this is where it gets beyond me). These will be copied into sheet 3 let's say starting from cell A3 - I'll stick in a clearcontents on the range at the start of the sub as the results will be copied into an email and then can be cleared when I need a new search:

- Shift (always found in Column A of the sheet being searched)
- Job Number (always found in in Column B of the sheet being searched)
- Date of shift (always found in Row 1 of the sheet being searched)

So essentially it's a lookup / fin... Read more

A:VBA Search Excel Workbook - Multiple Values, Return Row & Column Header

Read other 16 answers
RELEVANCY SCORE 67.2

Hi There

Quite new to this. I am creating a macro to use create multiple charts. The idea being that I use column A everytime as the X axis and then columns B, C, D etc as the Y axis. Each graph will only have one series, i.e. chart 1 uses AB, chart 2 uses A and C, chart 3 uses A and D etc etc. I am attempting to use dymanic named ranges. My code is almost there however I have two problems; the first graph is produced fine. The second graph however includes colum B as a second (unwanted) series, the next graph includes columns B and C, etc etc. Why is this as they are not included within the named ranges? Secondly, the first graph comes out with a chart title fine however the subsequent graphs do not. Below is my code...

Sub charts()

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
myrangeAREA = Selection.Address

Range("b1").Select
Range(Selection, Selection.End(xlDown)).Select
myrangeRWE = Selection.Address

Range("c1").Select
Range(Selection, Selection.End(xlDown)).Select
mrRWECHA = Selection.Address

Range("d1").Select
Range(Selection, Selection.End(xlDown)).Select
mrtest = Selection.Address
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=327, Top:=75, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, myrangeRWE)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

With ActiveSheet.ChartObjects.Add(Left:=150, Width:=327, Top:=125, He... Read more

A:Excel VBA macro - multiple charts

Can you post your workbook?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 67.2

Give me a hand to impress the boss!

I have a column which has a list of different values. For each value in this column i wish to create a new sheet and name the sheet the same name as the value in the column.

For this i have set up a do/while loop , however, after about 27 turns, it stops.

My guess is that it is something to do with the sheet Name property, or the number of sheets that are there. i.e. in the VBA project box on the code screen, it appears like this
Microsoft excel objects
-sht1(main)
-sht11(value)
-sht111(value1)
-sht1111(value2)
etc until
-sht111111111111111111111111(valuex)

Here is a summary of the loop;

Dim strValue1, strValue2 As String

Do While intCounter < intNumSheets
strValue1 = ActiveSheet.Range("E" & intCounter).Value
strValue2 = ActiveSheet.Range("E" & intCounter + 1).Value

If strValue1 <> strValue2 Then
Application.CutCopyMode = False
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = ActiveSheet.Range("E" & intCounter + 1).Value
End If
intCounter = intCounter + 1
Loop

Email me
 

A:Excel Macro - Multiple sheets

Yep. Bad news when that occurs. After while, you won't be able to open the workbook. You must be using 97, huh?

Each time you make a copy of a copy, it adds the one.

Try doing it this way instead:

Create yourself a sheet you'll use as a template.
Copy ONLY that sheet each time you create a new sheet.
You can hide the template sheet.

Don't ask me for the code, LOL!
I don't code, but I can troubleshoot it pretty well. I know that makes no sense...
 

Read other 1 answers
RELEVANCY SCORE 66.4

Hello!

I wonder if someone could help me? I'm trying to combine multiple rows into a single row. My data currently is formatted:

E-mail,First Name,Last Name,Event,Ticket Name,Spaces
[email protected],John,Taylor,C,81,5
[email protected],Mary,Smith,A,81,2
[email protected],Mary,Smith,D,144,1
[email protected],Mary,Smith,I,81,1
[email protected],Mary,Smith,G,82,1
[email protected],John,Taylor,A,81,2

I'd like to be able to combine rows so that my data looks like this:

E-mail,First Name,Last Name,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces
[email protected],John,Taylor,C,81,5,A,81,2
[email protected],Mary,Smith,A,81,2,D,144,1,I,81,1,G,82,1

I found a macro on an old techguy thread which just about does the job, only it keeps repeating the last name and doesn't reprint the header. I've tried to edit it but I know nothing of Visual Basic and can't figure out how it's working. Here's the code from that thread:
Code:
Sub test()
MainSheetRows = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B1:B" & MainSheetRows)
x = WorksheetFunction.CountIf(Range("New!B:B"), Cell)
If x = 0 Then
y = WorksheetFunction.CountA(Rows(Cell.Row))
Cell.Offset(, -1).Resize(, y).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
y = WorksheetFunction.CountA(Rows(Cell.Row)) - 2
z1 = ... Read more

Read other answers
RELEVANCY SCORE 66.4

Does anyone have a macro that can be used "generically" in different workbooks with different number and names of sheets, to protect all the sheets at one time.

I'm getting tired of having to protect or unprotect one sheet at a time in workbooks that have anywhere from 4 to maybe 14 sheets, when I'm making design changes.

So far, the only macro I've been able to come up with is:

Worksheets("January").Activate
ActiveSheet.Protect
*** Repeat for each sheet by name***

Of course this won't work from my "Personal" macro library on other projects because there are a different # of sheets, and they're not named the same.

Thanks!
 

A:Excel Macro to protect multiple sheets

Read other 8 answers
RELEVANCY SCORE 66.4

Hi,

I have a large excel worksheet, divided into 4 rows (different data fields). Hard luck, in one of the rows, some cells contain two products or more instead of one (ex: "ZB3054066¦¦ZB3601207¦¦ZB3601206"). They are separated either by a blank or by a ¦¦ (double bar).
I need that each time I detect a cell in that particular row that contains multiple products, I create new rows so that each row contains only one product in the column "component parts" and that the original data in the other columns remains unchanged.
I don't know anything about excel macro, but here is the approximate code I created to do this task :
If current row cell "component parts" contains a separator (either or blank),
{
create "number of separators" new rows
copy every field except the "component parts" one
while( cell "component parts" contains a separator)
{
cut the part of the "component parts" cell before the first separator (including the separator)
paste it into the next row "component part" cell
delete the separator in the new "component parts" cell
}
}

If anyone knows how I could do this in an excel macro...
Thanks.
 

A:Excel macro : cell with multiple content

Read other 16 answers
RELEVANCY SCORE 66.4

Hi all,

the title of the post only gives part of the story.

What I'm looking for is a VBA script to attach to a button. When the user clicks the button I need each of the worksheets within the workbook to be checked for an entry (say in cell A8), and to only print the ones that have a value (the value will be different on each sheet, so another way to look at it is a check that does not print worksheets with no value in that cell).

For example: The workbook has 5 worksheets to check (A, B, C, D and E). A8 is completed on sheet A, D and E. When the user clicks the button, sheets A, D and E are the only ones that should print.

Hope I have been clear enough with what I need!

Thanks in advance for any help you can provide.
 

A:Excel - Need macro to print multiple worksheets

Hi welcome to the forum.

I have a piece of code which I found and have been using since.
It's in the sample sheet and you will have to workout some things for yourself like the filetr for the sheets you wish to print.

There is a simple explanation in Sheet3 and 2 buttons which can help you on the way.

The code is as is.

Happy coding

PS excuse my mispelling greased
 

Read other 3 answers
RELEVANCY SCORE 66.4

Hello,
I am having some issues trying to figure out how to select multiple cells using macros. My goal is to be able to highlight a certain name in a column of names, and this name can appear multiple times and not always in a set range.

Example:
John
Larry
Shane
Larry
Larry
John
Larry

(The blue font indicates a highlighted cell)

Is their a way for a marco to look at all of the names in column A until "Do Until IsEmpty()" has been completed and have the specified name in the macro "Larry" stay highlighted to view individual rows of information. Also, these names will not have blank cells inbetween them.

Thanks for all the help!!!
 

A:Excel Macro Selecting Multiple Cells

Read other 6 answers
RELEVANCY SCORE 66.4

Hello!

I've been tasked to compile in a sheet the monthly ending balances (from July to Dec) of customer receivables for 493 CUSTOMERS. That's a lot! I already started, but I know continuing until the end is crazy and even impossible. So please help?

Below is a picture of the first sheet containing the ledger data of the 493 customers dated July to December. I just blocked the center and changed the customer names in an attempt to maintain confidentiality.

As an example, for Customer 1, I need the July, Aug, Sept, Oct, Nov, and Dec ending BALANCES (last column) of the receivables.

(I ALSO ATTACHED FILES OF THE FF 2 PHOTOS WHICH I UPLOADED IN FLICKR JUST IN CASE)

For july 31, since the first entry is already 08.15 or August 15, this implies that the July balance must be 0 which I have to input into the July 31 column in the second sheet as shown in the second picture.

For Customer 1 August 31 2012, the closest date to August 31/8.31 entry indicates a balance of 0 so this must be the August 31 balance.

For customer 1 Sept 31 2012 to November 2012, no entries are inputted for these months (9-11) so the August 31 balance mustn't have changed and is the standing balance as of November 31.

For Customer 1 December 31 2012, the outstanding balance is 0.

Oh, as an added note, just in case, debit adds to the balance and credit deducts from it.

I've had to manually mine the ending balances for 90+ customers so far. HUHUHU. I just know macros and YOU wil... Read more

A:Macro Help: Macro for Copying Certain Values Using Certain Criteria

It will be easier if you make sheet1 like a database table.
That is
Column A is all customer name with no blank rows.
Column B is the invoice date
Debit and Credit can be in 2 col or 1 col.

Try not to "beautify" your source data with empty rows and columns, it makes summarisation difficult.

When all is done, you may use sumif to sum all values before a particular date.
 

Read other 1 answers
RELEVANCY SCORE 66

Hi Guys,

First of all thanks for the great support provided by your forum!

I'm using with remarkable profit the following macro edit by Rollin_again (sorry in case the nick is wrongly written!)

PHP:

[SIZE="5"]Sub ReplaceText()



For 
Each vCell In Range("A2:A200").Cells



Columns
("C:C").Cells.Replace What:=vCellReplacement:=vCell.Offset(0Read more

A:Macro Multiple Find and Replace in Excel 2007

To get a green fill the code you could use would be
vCell.Interior.Color = vbgreen

but is you place this before Next cCell, I think all the cells for vCell would turn green.

You turn the colouring back to normal by using

vCell.Interior.Color = xlNone

Give it a shot
 

Read other 3 answers
RELEVANCY SCORE 66

Hi Guys,

I'm using with much profit a code that I have found in this forum:

Sub ReplaceString()

StartRow = 1
EndRow = 10
For i = EndRow To StartRow Step -1
Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Acutally this search coloumn "C" for values in Coloumn "A" and if found replace those values in "C" with what you have in "B:".
Example:

Before MACROAA1ABB1TCC1NB1B2B1After MACROAA1A1BB1TCC1NB1B2B2

what I need is the replaced value in "C" be highlighted as already in the example (iether by red fonts or green interior is ok).
The code need an addition to do that.

Could you help me please?

thanks!


 

A:Macro Multiple Find and Replace in Excel 2007 (2nd leg)

Hi, Why not post a sample file with the code you're using and also show what you need.
It's easier to work with a sample file.
Make sure the data is dummy data
 

Read other 1 answers
RELEVANCY SCORE 66

Hi I need a Macro to support the following:

Search Column C for cell(s) containing the content of B1 and then replace all instances of B1 (located in Column C) with the content of Cell A1. and so on, and so on.

I found what I believe to be the solution here in an old thread below although the solution does not appear to be attached! Please help.

http://forums.techguy.org/business-applications/1032256-solved-macro-multiple-find-replace.html
 

Read other answers