I have a list of strings as such
A1: A
A2: B
A3: D
A4: E
How can I check to see if this range is sorted with vba?
I have searched but havent found anything for strings.
I can only think of adding each cell to an array, creating a copy array, sorting the copy array and then comparing the two arrays. I want it to return TRUE, FALSE or 1, 0
I'd like to write a formula that check if a string (contained in another cell - say A1) includes at least one occurrence of one of a set of 5 sub strings. If the substring is included then it should return which one.
For istance, say that the cell A1 = " The colour is BLACK" then I'd like a formula in cell B1 that check if any of the following strings is included in A1 (RED, GREEN, BLUE, BLACK, ORANGE) and that tells me which one.
How to do this with a one cell formula ?
I have 2 sheets of data. I am looking for a macro that will check if there are any strings that exists on Sheet1 but not on Sheet2 and if they do, paste them at the end of Sheet2. All of this data is in Columns A by the way.
View 6 Replies View RelatedCan the below be achieved with any formulae or macro -
I have 2 worksheets (sheet1 & sheet2). In sheet2, i have some colors in column A (e.g. "red" in A1, "blue" in A2, "green" in A3.
In sheet1, i have some text strings in column A, e.g.
A1: I love red color
A2: my pen in blue
A3: Green grass
A4: Orange juice
A5: I like red and blue
What i want in corresponding cells in column B, is the name of the color which is in column A. so my output should be
B1: red
B2: blue
B3: green
B4: "NIL" (any error message)
B5: red (even though it contains 2 color, i am happy to show any one of the colors it contains)
I have strings like this:
"xx-xx-100-ap01"
"xx-xx-200"
"xx-xx-200-ap01"
"xx-xx-200-ap02"
etc
I'm running a loop on all cells, and checking different values in each row where this strings occure.
But I would like to do a IF check on this string. If it find a string with 'ap' in it, it should do my check.
My problem is to understand the combinations of right, len, mid, instr etc. How could I use this functions to do checks in the strings.
I have some code traversing a directory of folders and subfolders on a shared server and returning a list of those file paths. Around 6000 folders with about 2 subfolders on average. This returns a list of 12000 filepaths but takes around 13-15 minutes to do. Each path looks something like this:
C:Folder1
C:Folder1Folder1SubFolder1
C:Folder1Folder1SubFolder2
C:Folder2
C:Folder2Folder2SubFolder1
C:Folder2Folder2SubFolder2
I have a range that contains 3 cells on top act as titles ie TYPE NUMBER and AMOUNT.
I need to extract a range from the original range where the TYPE changes.
eg
TYPE NUMBER AMOUNT
OSF 1002 10.23
OSF 1004 15.68
SA 1006 100.25
SA 1005 17.89
CW 1112 15.56
So for the above example I would need the 2 OSF entries and its corresponding NUMBER and AMOUNT to another location on the worksheet. The same for the other TYPEs. The number of entries for each TYPE varies so I will not in advance how many entries of each TYPE I will end up having.
I 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 recently had a query about pulling data from different closed workbooks using a concatenate to easily choose the file:- ....
View 6 Replies View RelatedI am trying to develope a search macro to go through sheets of a book looking for strings separated by comas in an Inputbox. For example, The data in the inputbox may be inputed as: ahu-1,vav1,erv-2,etc(Number of items may vary). Then to assign variables(range) to each in order to use the "Find" function.
View 2 Replies View RelatedI have a series of incident reports (some of which are injuries), and I'm calculating the number of incidents per body part per month using:
=SUMPRODUCT((ISNUMBER(FIND($D4,owssvr!$Q$2:$Q$2188)))*(MONTH(owssvr!$A$2:$A$2188)
=MONTH(math!F$2))*(YEAR(owssvr!$A$2:$A$ 2188)=YEAR(math!F$2)))
In which D4 is the body part (Neck, for example), owssvr is the sheet with the records being summarized and F2 contains the month being queried.
The problem I'm having is that I want to calculate data for a region of the body (head & neck), which will include count any record that has a part of that body region (nose, face, eye, tooth) mentioned in the affected area text. If I simply sum all the values calculated for each body part, records that include multiple parts (e.g. "scratched nose and eye") will be counted twice.
Can I calculate whether a range of cells for the incidents contains any of a specified range of body parts (listed in D4:D15), but do this for each month, and count each record only once (e.g. "cut nose" = 1 record, "cut nose & eye" = 1 record).
I am using the following code to search a database of information and then display it on a different sheet.
The user types the search term into a textbox and then presses a command button to search the database.
Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?
Sub SearchDatabase()
Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range
On Error Resume Next
I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
I am having no success using either: 1) COUNTIF with wildcard (*) or 2) Case Statements to find text strings in a cell. I've even tried using Copy/Paste Special values to change the way Excel 2007 interprets the field.
The Do While loop looks at target cells in Col. 18 which contains one or more specific last names. I want to put a text string (also a name) in Col. 19 IF the target cell contains the last name in the COUNTIF or Case Statements. The COUNTIF normally puts a TRUE or FALSE based on the condition, but I'd like to put the the last name based on which condition is met and then continue looping to Row 3. It's a small number of rows, less than 100.
Here's the Case statement ...
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]....
Im new to Macros. Im trying to find a string of text assign that to be the top of the data and then find a different string and assign that to be the bottom of the data. Then run a loop whilst inside that data range. Am i going about it the right way? Attached is a sample data file.
View 4 Replies View RelatedI 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.
I receive data from different sources and one column is usually in upper case, but I prefer to use proper (title) case. The problem is that the data frequently contains text strings that should remain in upper case or should combine upper and lower case letters. Examples follow:
Data received:
COROLLA AE90, AE92
COROLLA SX, GTI
Data should look like:
Corolla AE90, AE92
Corolla SX, GTi
Note that 'GTi' is upper case 'GT' and lower case 'i'.
I've set up a spreadsheet 'CaseConverter.xls' as a look-up, with a range ('SpecialCase' - A2:A65536) featuring names, codes and acronyms represented correctly. Is it possible to use a macro to run Excel's global replace to search for upper case strings in one spreadsheet and replace with the correct strings from the other spreadsheet? The macro would have to loop through the entire 'SpecialCase' range and look in the supplied spreadsheet for versions of the data in the special case range without being case-specific. The supplied spreadsheet (see attached for example data) is called 'Application.xls'. Column B is what I'm trying to reach.
I have a column of data in the following format:
Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)
etc
I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.
For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.
I can do it in excel by:
(Assuming data is in cell V36)
'in colum x
=FIND("(",V36)
'in column y
=FIND(")",V36)
'in column z
=MID(V36,X36+1,Y36-(X36+2))
in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)
I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.
I am very comfortable with VBA but I usually avoid using formulas if at all possible (unfortunately, I have, formulas are unavoidable (by request).. so formulas it is)
I will try to explain
Column A is a list of dates (ever expanding)
Column B is a list of names ( multiple entries of various names but aligned with various dates)
Column H is a list of names (each possible name which can appear in column B, no duplicates)
Cell I1 = A Date
Cell J1 = A Date
(forming a date range)
Basically what I need to do is:
= COUNTIF(B:B,H2) - but only count it if it is withing the date range (>=I1 & <=J1)
I have tried wrapping it in an IF statement but I can't seem to get it to work.
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 have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
View 1 Replies View RelatedNeed 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 have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
=COUNTIF(D$5:D$8,OR("a*","b*","c*"))
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick
ballgame
stick ball
tenballs
green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
I've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
How would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
View 4 Replies View RelatedI am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
Is there a way to do this with the VBA code.
make this formula more concise and shorter, it was design to check a numbers in a range to see if any of them falls into a particular range.
=IF(SUM((COUNTIF(Fund,">11999")-(COUNTIF(Fund,">12999"))),(COUNTIF(Fund,">21099")-(COUNTIF(Fund,">28729"))),(COUNTIF(Fund,">28730")-(COUNTIF(Fund,">33999"))),(COUNTIF(Fund,">58999")-(COUNTIF(Fund,">59999"))),(COUNTIF(Fund,">82000")-(COUNTIF(Fund,">84999"))),(COUNTIF(FUND1,">11999")-(COUNTIF(FUND1,">12999"))),(COUNTIF(FUND1,">21099")-(COUNTIF(FUND1,">28729"))),(COUNTIF(FUND1,">28730")-(COUNTIF(FUND1,">33999"))),(COUNTIF(FUND1,">58999")-(COUNTIF(FUND1,">59999"))),(COUNTIF(FUND1,">82000")-(COUNTIF(FUND1,">84999"))))>0,"ATTACHMENT E IS REQUIRED","")
and if the any of the number fall into the range it will print the message, "Attachment is Required"