I 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 RepliesAs enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,
Total Amount = Amount X Exchange Rate
Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head
75698 Dog & Duck
634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
converting text strings to dates.
The dates I have imported into a spreadsheet are of the format:
11.05.08
I use code to convert it to this format
11/5/08
But with some of the dates, I get
11/5/2008 whereas with others, I get
11/5/08
However, if I recorded a macro the date format appears as
11/5/2008 for all the dates.
But the code is excatly the same, if I
see below:
My
For Each celld In Range([e1], [a1].End(xlToRight))
If celld.Value Like "*DATE" Then
celld.EntireColumn.Select
On Error Resume Next
Also, how do you convert strings to dates using VBA?
I thought DATEVALUE performed this function but keep getting errors when i try using it.
I need to separate out some rows by date into new worksheets, but I need the dates to be a range. Our cycles are separated into periods, which last about 1 month, so I need one new worksheet for each period. I was thinking about having like a cover sheet that defines the exact dates (like column A has the period title "P1, P2, P3,..." and column B has the start date and column C has the end date).
Then on the next sheet is where I would keep my Input data. So I need to separate the rows of the input data sheet out by the periods. The date for each data entry is kept in column M. I have attached a sample workbook explaining what I tried to describe above.
Is it possible to separate a range of cells with numerical values into "n" groups that have equal (or as close to equal as possible) total sums?
Ex. The range A1:A30 includes 30 random numbers between 1 and 12 (obviously there are duplicates). I need excel to auto-generate 6 groups of 5 cells each with each group having the same (again, as close as possible) total sum. Preferably, I'd like the numbers in each group to be similar from top to bottom, but I'm not overly concerned about that.
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 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 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'm currently trying to copy a dynamic range based on the row number of a selected listbox item. I'm able to get the row number of the selected listbox item from the data tab in my file. I, however, am stuck when it comes to copying a fixed column / varying row range from this tab and copying it over to another tab.
Private Sub CommandButton2_Click()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long
Dim SearchTermsStr As String
Dim SearchTermsRng As Range
[Code] ....
SearchTermsRng is returing "Nothing" but the SearchTermsStr returns "F3:O3"
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 had this code and it works like a charm, except, it crashed if the data gathered is just one element due to transpose ..
VB:
Function getAR(c1 As Range, c2 As Range, w As String) As Variant
Dim s As String
Dim arrTemp() As Variant
Dim arr() As Variant
[Code]......
I 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.