Excel 2010 :: Search For A String In Contents Of Range Of Cells

Sep 26, 2013

I have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.

I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.

Excel 2010
A
B
C
D
E
F
G

[Code]...

Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.

In the example shown I'd like 01-Sep to appear in B2.

I'm using Excel2010 and Windows 7.

View 3 Replies


ADVERTISEMENT

Excel 2010 :: Clear Contents Of A Range Of Contiguous Cells

Jun 4, 2013

I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.

I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.

If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.

View 6 Replies View Related

How To Search For Duplicates In A String Across A Range Of Cells

Apr 7, 2009

if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?

So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.

Columns A - D will contain a string of more than one character so it needs to search the string.

e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213

I would like this to return true because the A cells do contain the values in the E cells.

I guess this is like looking for duplicates but with a search string twist!

View 14 Replies View Related

Search For Part Of String In Range Of Cells

Jan 17, 2012

Following problem:

I have a list of changes on our bankaccount.

Sometimes, in the description a payment we receive the name of the person who transferred the amount is mentionned.

It can be anywhere in the string, it can be just his/her first name, it can be his/her full name...

I also have a list of all our clients with first name in column A, second name in column B

Something like this:

string from bancaccount:

EUROPESE OVERSCHRIJVING VAN BE16 6712 5615 7974 BANKIER OPDRACHTGEVER: EURBBE99 VANDERSMISSEN WILLY VAN PLAKSTRAAT 176 9000 GENT 68/10762827 PENSIOEN REFERENTIE: OV-0000236-00240

client list
Francois D'hondt
Germaine Canipel
Willy Vandersmissen
Karel D'hondt
.
.
.

I'd like VBA to search in the clientlist for names that occur in the bankaccount string.

Sice ther might be a lot of Willy's in the client lsit I think it will be best to serch for the combination of both first and second name.

Once found the name the procedure should return the rownumer on which the client can be found in the client list.

View 1 Replies View Related

Search Range Of Cells For Alphanumeric String

Mar 5, 2010

I tried to find out how to post my table but the link in that thread lead me to a notice saying I wasn't allowed access to the page.)

Cells A2:G2 contain various alphanumeric strings.

If any cell contains "SD#" (# = a number which might include decimal places) then I want to extract the number.

If more than one cell contains "SD#", I want to add the numbers together. The total goes in cell C6 and it needs to change if the contents of A2:G2 change. E.g. if A2 shows SD4.75 and B2 shows SD8, the total in C6 would be 12.75

So I'm looking for a formula for C6....

View 11 Replies View Related

Excel 2010 :: Using Find Function On A Range And Search For (,) Character

Jan 22, 2013

I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.

I tried just recording a macro using the find function but no code shows up.

how to use the find feature in a macro, can this be done?

excel 2010

View 4 Replies View Related

Excel 2010 :: Moving Numbers From String Of Text To New Cells

Feb 8, 2013

I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.

Here's a few examples of what I mean:

Original text
RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12
MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38
RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01

would like to split into...

Cell 1
RED008 - Wickaman and Hoodlum bandwidth
MFR005EP - Various Artists bandwidth
RAZORS010 Future Cut bandwidth

Cell 2
(0.06Gb @ 2.00)
(0.19Gb @ 2.00)
(0.01Gb @ 2.00)

Cell 3
0.12
0.38
0.01

View 4 Replies View Related

Macro To Search String In A Range Of Cells And Replace While Leaving Original

Jul 25, 2014

So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.

View 6 Replies View Related

Excel 2010 :: Macro Based Search Function Using Data From 2 Cells

Mar 15, 2012

I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.

What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.

Using: Excel 2010

Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell

[Code] ...........

View 2 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Excel 2010 :: Color Fill A Range Of Cells If Specific Cells Not Blank

Feb 7, 2013

I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.

I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.

View 7 Replies View Related

Excel 2010 :: Put Named Range In Column Of Cells?

Jun 21, 2014

using excel 2010

I created named range selection called "Contractors".....how do I put the drop down lists in a column of cells now?

View 2 Replies View Related

Excel 2010 :: Shade Range Of Cells Based On Particular Cell Value?

Feb 17, 2013

I'd like to shade a range of cells based on a particular cells value.

Specifically:

If cell E3 is JUNE then I'd like the range of cells from B10 thru I10 to be shaded.

If E3 is JULY then it would shade C10 thru I10....etc.

Excel 2010

View 9 Replies View Related

Excel 2010 :: Copy And Paste Cells Within Date Range

Mar 27, 2014

I have on sheet "Charts" in cell "B20" a "date from" and in cell "C20" a "date to" (these will be input manually).

What I need is to check Column "F" on sheet "Report" for any cells within those dates, I then want to copy the entire row(s) that contain these dates and paste them into sheet "Weekly" starting at cell "A2".

Using excel 2010

View 4 Replies View Related

Excel 2010 :: Comparison Of Contents Of Two Tables

Oct 31, 2012

I have Table1 in sheet1 and Table2 in sheet2 of same Excel Workbook. The contents ( Data in columns B, C, D, E and F) of Sheet1 Assets are to be verified with respect to the contents ( Data in columns B, C, D, E and F) of the same Asset in Sheet2. If the all contents (of all columns) are matching, it should return Boolean value (True) in column G of the sheet1 or else Boolean value (False).

I am using Excel 2010 version and I do not have Power Pivot menu.

Table1:
Excel 2010
A
B
C
D
E
F

1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue

[Code] .........

Table2:
Excel 2010
A
B
C
D
E
F

1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue

2
U1101JBM
Y
Y
Y

[Code] .....

View 4 Replies View Related

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving?

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010

[URL]

View 1 Replies View Related

Excel 2010 :: Expand Text Number Range Into Separate Cells

Feb 20, 2014

Using Excel 2010.

I have data in excel which looks like this:

Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863

I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.

Column 1 1200
Column 2 1201
Column 3 1202

Like that only. Is it possible?How?

View 4 Replies View Related

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.

I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...

Auto lock cells after data entered and SAVED.

Auto Lock Selected Range of Cells After Saving

I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.

This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.

View 1 Replies View Related

Google Image Search From Cell Contents Of Excel File?

Apr 5, 2014

i have an excel file with hundreds of rows of product codes which i need to download images for. is there a way this can be done directly from excel? basically if cell A1 has product name and Cell B1 have product code, id like Cell C1 to generate a link that will use the contents of B1 as the basis of my google image search. can this be created using a macro?

View 9 Replies View Related

Excel 2010 :: Making Chart Title From Cell Contents?

Jan 10, 2014

I am trying to make the contents of a cell into a chart title.I know I can do it like this...

ActiveChart.ChartTitle.Select
Selection.Caption = "=tab_name!R2C1"

However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...

ActiveChart.ChartTitle.Select
Selection.Caption = "=R2C1"
or
ActiveChart.ChartTitle.Select
Selection.Caption = "=A2"

Is this possible? I am (kinda new at) using EXCEL2010.

View 3 Replies View Related

Excel 2010 :: Copy Contents Of Column Based On Value In Another Cell

Jul 30, 2013

I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.

I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.

View 2 Replies View Related

Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells

Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

View 1 Replies View Related

Excel 2010 :: Use Range Of Cells With URLs To Files And Check It They Exist - If False Delete To End?

Oct 24, 2013

This is Excel 2010 on Windows 7

Trying to figure out a VB Script but don't know where to start.

I have a sheet for each month. lets take September 2013

A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)

I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows

View 3 Replies View Related

Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box

Aug 13, 2012

Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.

Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names

E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown

The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.

View 3 Replies View Related

Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table

Dec 2, 2013

I am using Windows 7 and Excel 2010.

Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?

I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)

Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......

Here is the table:

I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green

LEGEND
Earned Value Limits

Milestone
RED
Yellow
Green
Turquoise
Blue

M2-M3
2.15

M4-M6
1.66

M7-M11
1.26

View 2 Replies View Related

Excel 2010 :: Copy Cell Contents From A Permission Protected File

Apr 17, 2013

S/W Environment: Excel 2010

Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?

View 1 Replies View Related

Search Contents Of Cells In Row From List Of Text Values On A Separate Sheet

Jul 13, 2006

If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:

I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet

View 3 Replies View Related

Excel 2010 :: Add Search Box To The Top Of Page?

Jun 12, 2013

I have an excel 2010 file that we export (save) to HTML so we can upload to our website.

1) Can I add a search box to the top of the page?

2) Can I freeze the row headings?

View 1 Replies View Related

Excel 2010 :: Search Gets Into Infinite Loop?

Feb 5, 2013

I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.

This is Excel 2010.

View 2 Replies View Related

Excel - Search For A String And Update Few Values Access?

Aug 3, 2012

I have a requirement there is a excel sheet that has multiple sheet with application names , cntact names , phone numbers & email address and this gets updated by me often. I need a code that can put the application names and email address alone into an access database and that should automatically get updated whenever changes are made in the excel sheet.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved