I need to loop through a worksheet to find the following product ID's: 100805, 6950000, 853000 and 20994000. Each time I find the product ID I have to execute the same code in that part of the worksheet to extract data.
I just do not know how to set up the macro to loop through each ID.
I have 12 Workbooks (each for every month) name Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
These workbooks contains something like the following
Name
Days of Vacation
Something Else#1
Something else#2
Something else #3
[Code] .....
I want something to loop through the months and copy all rows for mary in a summary sheets and sum the Days of Vacation from Column B.
Note that The first Workbook has some data, the 2nd Workbook is the Jan and the 13 Workbook is the Dec, the 14t is the Summary Workbook
VB:
Sub SearchForString()
Application.Calculation = xlCalculationManual
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
LCopyToRow = 2
[Code] .....
How would I loop through all series collections on a chart?
View 3 Replies View Related am looking for a code that uses for loops of any other type of loops to create an array shown below:
1 2 6
6 5 1
2 3 7
7 6 2
3 4 8
8 7 3
5 6 10
10 9 5
6 7 11
11 10 6
7 8 12
12 11 7
The array above represents the node number of the triangles within a rectangle as shown in the image. [URL] .....
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies View RelatedI'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.
I'm using control + F to open the search box.
It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.
So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.
It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.
I have a set of 4 columns with YES/NO statements in them.
I need to find any YES strings, of three or more, within one column, uninterupted by a YES in another colum.
EXAMPLE:
_____A ___B__C__D__E
1 1/1/2008 YES NO NO NO
2 1/2/2008 NO NO YES YES
3 1/3/2008 YES NO NO NO
4 1/4/2008 YES NO NO NO
5 1/5/2008 NO NO NO NO
6 1/6/2008 YES NO NO NO..................
What I'm trying to do
A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?
I need C1 to return:
how your
Is this possible?
I have several cells in a column that look something like this:
Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s
Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?
I am currently trying to write a macro that will search a column for different strings. These strings are inside the cell (as in it is not the only value in the cell) and then copy the entire cell over to another column, then delete the two cells to the right of the originally found cell.
Heres an example of what I want to:
There are four columns, the first has a name with both the first and last (amanda white)the second just the first name (amanda), the third just the last name(white), and the forth an organization name. if the first column contains an organzation name, it has to clear out the first name and last name columns, and copy the name column to the organization column.
NAME | FIRST | LAST| ORG|
amanda white amanda white
mike jones mike Jones
pizza hut pizza hut
I have a list of organization keywords to search the first column (e.g. enterprise, variety, management, pizza). I want it to recongnise the "pizza", copy that entire cell over to the organization column and delete the first name and last name for that row.I've been trying to modify a code like this but i can't seem to make anything work.
VB:
FindWhat = "pizza"
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp)) [code]....
I have been given a huge membership list. The field for the CITY also as the two letter state abbreviation (e.g., "Fremont, CA" instead of just "Fremont".). I want to be able to have the "CA" or "WA" or "NV" (etc) from the city field appear in a new STATE field. I successfully use the below statement to do this with the "CA" but I want a statement that will search for multiple strings (the other states).
Here is what works now: =IF(FIND(" CA",F2),"CA"). But I want to be able to add other state abbreviations to this.
I am having a hard time creating a code that would search the exact string in a cell. See sample scenario below.
1. I have a string "hello" to be searched.
2. I will search the whole string in each cell in a previously declared range.
3. If in a cell i found "kdhlsk hellodsiewjls", although "hello" string is there,
the code should still return 0.
4. But if in a cell, i found "kjdsfel ljsdlkue hello luoeww", then the code should return 1.
I have already created a code, but it will always return 1 in step#3.
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
View 5 Replies View RelatedI'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
I have a series of numbers in cells A1 to A5, e.g: 5,15,19,3,105 now my target value is 23. Is this possible that we can come to know which values we can sum to get our target value. in this case cells sum of A1,A2,A4 is 23 which is my target value.
in the same way i will set other target value , it should show which are the number which should be summed to get my target value.
I have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?
E.g.
Before: [ +Test +Test]
After: [+Test +Test]
Code:
Columns("D:D").Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...
Row 2: 12, 100, 43, 1444, 141, 332
Row 5: 25, 140, 55, 3000, 244, 440
Which number in row 2, is the lowest percentage of the total possible points found in row 5?
B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.
I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.
In have a worksheet with about 5,000 rows. In column there is a unique identifier for each row, however the unique identifier can take one of 12 different formats, namely:
L1314LAND00001 through to L1314LAND0XXXX
L1314CUST00001 through to L1314CUST0XXXX
L1314COMM00001 through to L1314COMM0XXXX
M1314LAND00001 through to M1314LAND0XXXX
M1314CUST00001 through to M1314CUST0XXXX
M1314COMM00001 through to M1314COMM0XXXX
N1314LAND00001 through to N1314LAND0XXXX
N1314CUST00001 through to N1314CUST0XXXX
N1314COMM00001 through to N1314COMM0XXXX
S1314LAND00001 through to S1314LAND0XXXX
S1314CUST00001 through to S1314CUST0XXXX
S1314COMM00001 through to S1314COMM0XXXX
These unique identifiers won't always appear in sequentiual order. In Cells B2 to M2, I want to be able to determine what the next entry for each of the twelve variations will be, so for example, if the current highest value in column A for the 'S1314LAND' range S131400421, in cell K2, I would want it to display S1314LAND00422, and if the current highest value in column A for the 'M1314CUST' range M1314CUST0036, I would want cell C2 to display M1314CUST0037, so anyone at a glance can see the next highest unique reference for the relevant set, rather than having to go through the unique identifiers in column A manually to determine the next entry in the series.
I'm not really sure where to start, when there can be more than one aplhanumeric entries ending in the same number, e.g. S1314CUST00500 and M1314LAND00500.
is it possible to copy various cells from one worksheet to another. I have a cover sheet holding various jobs for different individuals, data validation has been used to set up various drop downs for the peoples name etc. On selection of an individuals name i was wondering could various cells on that row be copied into a seperate worksheet with that individuals name being the tab name? I have tried using the " Lookup" function but im not getting too far.
View 8 Replies View RelatedI need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
I have a approx. 70 excel sheets with thousands of entries. Within a single sheet there are some duplicate entries (based on an account number). I find the duplicate entries by using the excel 2007 conditional formating and then sort by color to only show the duplicate entries. There are often hundreds of accounts that are duplicates. Sometimes there are more than 2 identical account duplicates (could be 3,4 or more). In each entry there is a number representing the rating of said account. These numbers differ within the duplicates. Example............
I am trying to find a formula that will choose a "winner" and enter a "W" based on the highest rating within the duplicate entries and then ideally assign an "L" to the loser duplicate account(s). Sometimes there is no rating, other times the rating could be the same in which case any account (does not matter) can be assigned a "W" and the others an "L"
I have the following table of stocks with corproate action types and dates with a ticker / identifier per stocA1:
Type
B1:
ID
C1:
Date
[Code]....
I am trying to find the NEXT Dividendcorporate action that is CLOSEST to the current date. I've done some google trawling and found a few formulae that seem to work only if the identifiers or dates are in a particular order. ALso having trouble using TWO criteria
I'm holding a Reverse Auction where people pay a dollar to place a bid, BUT the twist is they are giving me a number that they how will be the lowest number but it has to be the only occurrence of that number (greater than 0).
During the party people can guess as much as they want to pay. I think i'll use a spreadsheet with their names in column A and go out in the row with however many cells for how many numbers they guess (so there would be blank cells in the overall range of the whole list if one guy buys 10 numbers and another only 1, for example).
So, i need a cell at the bottom that tells me the lowest number that wasn't guessed more than one time.
I've found how to FIND duplicates and the lowest number but i don't know how to write it so that it discards the duplicates.
I need to loop through worksheets in a workbook and copy every first cell value(A1) and then paste into a new worksheet.
I have tried various loops. some have copied first value for the first sheet and then pasted in the new sheet. while others have been not so good.
This is the code I have so far and this does not work at all.
Code:
Sub Check()
Dim ws As Worksheet
Dim lr As Long
Dim treg As Worksheet
[Code]......
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?