If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)
I don't understand what I am missing. but I can't paste a string into a cell [range.cells(x,y).text = string]
Here is my ' ' -delete any rows that contain "Fab 11"
' -rename any cell that has "Supply x" (Delete " supply x" from text)
Dim cellText As String
Dim i As Integer
Dim lastRow As Long
Dim rngSigac As Range
Dim wb As Workbook
Dim ws As Worksheet
I need to state whether a particular word, string or text in one cell, appears anywhere in a sheet.
Attached example: does the word AAAAAA in cell A10, appear anywhere in the range A3:A6
I need to be able to drag this formula so need a generic formula rather than adding specific text in speech marks to it.
As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.
[Code] .....
Here's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
View 5 Replies View RelatedI have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
I want to count the number of times a word appears in a range (like M9:S663), but sorting it by the month it appears (eg: january = 2, february = 56, march = 2000, etc.)
I managed to do this but there has to be a better way
=COUNTIFS(RNM.1;"PRUM Transcripcion";FechaComite;">=01/01/2012";FechaComite;"=01/01/2012";
FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";
FechaComite;"=01/01/2012";FechaComite;"=01/01/2012";FechaComite;"
I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.
Suppose I have the following sheet: (column C is what I am trying to achieve)
A
B
C
1
[Code]....
That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.
I have a worksheet with following values:
A
B
C
1
Shorts
75
[Code]...
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
I am trying to create report of the in and outs of tools we are shipping for repair.
Currently in Tab 1
Column F - Date shipped for repair
Column G- Vendor Name
Column H- Date Returned
What I need in Tab B
Vandor - Total Sent- In Repair @ Vendor- Total Repaired - Total Unrepaired
Here are Images if it makes it a little easier to understand
The Info I currently have
image1.png
The info I required filled out based on the previous data
image2.png
Below is the code I started:
[Code] ......
The range contains a list of names. I want the sub to start with the first name and sum the value(long) which are in 7 columns to the left of the text string. Once this procedure has been done for the first name, it should go back up to the second name and do the same and so on. Once done I will return the name (cell A1 in another sheet) and its respective sum (cell B1 in another sheet) so that I have the list of names with their respective sum.
Sum function using an offset of 7 columns to the left?
I was able to find the syntax to add a cell within a text string but I am having a formatting problem. The cells which I am adding were using formulas that left decimals. Although I turned off all of the decimals on the cell, the values when I used the cell within the text string included all of the decimals and in some cases 6 or more decimal places. Is there a way to keep the formatting of the cell?
View 11 Replies View RelatedI have a cell that is populated by clicking a button which pulls info from an external source over which I have no control. I need to confirm the selection made by the user contains one of four quarterly values: Mar, Jun, Sep or Dec. I have this but wanted to know if there is a better alternative or a way to shorten the code.
Code:
If InStr(1, UCase(celltxt), "MAR") Or InStr(1, UCase(celltxt), "JUN") _
Or InStr(1, UCase(celltxt), "SEP") Or InStr(1, UCase(celltxt), "DEC") Then
I want to remove a string of text from the front and rear of a cell value and would like to do it with one formula. I have tried using LEFT,RIGHT and LEN. I would Like to use the SUBSTITUTE formula as the user can define the actual string to be removed.
I can achieve want i want using two columns i would just like to be able to consolidate down to one.I have tried nesting the formulas but i always seem to get an error.
Characters to remove
Raw data
Output
Front
dog
dogcatmouse
cat
Rear
mouse
dogratmouse
rat
Assume that the table uses stadard naming conventions for Columns(a,b,c...) and Rows(1,2,3...)
I do have a price list for my inventory for each product code (only letters)
E.g. productA 100
productB 200
When I'm writing in the order list, I'm writing in a single cell product code + size e.g. productA 5, productB 7
I need to get the corresponding price for each cell.
I need to find a formula which will search a list of cells looking for a particular text string. If it doesn't find this text string it then needs to search for the next one. For instance if I had a list of product codes: ABCD1234, BCD1234, ABCD2345, CDE23456, BCD2345 I want the formula to look up and see if the cell contains the text ABCD and if so return ABCD as the value, if not I want it to go on and see if the cell contains BCD and return the value BCD, if not then go on and search the value CDE etc. It seems like an easy job to do manually but I have a list of over 3000 codes to do this.
View 4 Replies View RelatedI want a formula or function that will return a count value if a cell contains a string rather than returning a value if the cell equals a value. For example, =COUNTIF(A$1:A$19703,F1) returns a value to cells in column G if the value in column F equals any exactly matching value in A. I need a formula that works like the filter option "contains" so that if any cells in col A merely contain a string rather than exactly match or equal a string from column F I can get a count value returned.
View 9 Replies View RelatedI want to be able to use a macro to read cells from a certain column so that it will evaluate a text string untill it hits a blank space, then copy the text that it found. I'd like it to work on Excel 2003 or later
View 9 Replies View RelatedI am using VB and need to search each cell in the row for a string "U30". If it appears, then I need to grab that plus the next three digits, "U30XXX". Otherwise, I need to grab the last six in that row (=RIGHT(M2,6)). I am not sure how to structure the If statement in VB. I searched the forum and help files, but was not sure what to do from here. Currently, I have...
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[4],6)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2", Cells(Rows.Count, "I")), Type:=xlFillDefault
I have a column of cells that contain a long string of characters....I need to pull out text from this string and put it in subsequent columns. The symbol I am concerned with is >> For example.
If this string is in the first cell in column A:
>>abcd>>defgh>>ijkl
I would want a formula to return abcd in column b, defgh in column C, and ijkl in column d. >>this is a test>>make it work. in this example I would want "this is a test" returned in column B, and "make it work" ruturned in column C
I am trying to put text form 3 cells into 1 string. I got the 1st 2 but the 3rd I only want to extra the first part of the cell up to the 1st comma. This is what i have so far:
[Code] ......
For example cell E81 would contain "abc, def, ghi" . I want to add to the string above only "abc" and not the entire cell content.
I'm trying to write a bit of to take a text string collected from an inputbox, and paste it into a specific cell.
Should be easy but where my text string from the inputbox is "XYZ", when it enters it into the required cell it enters it as " ="XYZ" ".
Attempt at code is below -
Code:
Sub EmailEdit()
Dim Response As String
Response = Application.InputBox("Input administrator email address", , , , , , , 2)
'Check to see if Cancel was pressed.
If Response = "" Then
[Code] .....
How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?
I have 3 columns of cells A,B,C.
Column A has dates in it.
Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")
Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.
I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.
Here is an example of what appears in one cell - this is exactly how it appears:
ASAI Los Angeles (XX , CA
DFO Pacific (XX ONLY), CA
DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA
SAI Los Angeles (XX ONLY), CA
Ldr Los Angeles El Segundo POD (XX ONLY), CA
Ldr Los Angeles Long Beach POD (XX ONLY), CA
Ldr Los Angeles POD (XX ONLY), CA
Senior Ldr (XXXX) Washington (XX ONLY), DC
What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.
How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.
I need A VBA that will delete a cell if it contains a specific text string
As long as the cell content is deleted, I can do the rest that needs to be done.
I am trying to transfer data from some appointment based software into a spreadsheet .
the data comes out as one row For example ,the following is the contents of cell A1: 06/06/2014 09:00 AM - 09:30 AM Patient: John Smith
What I would like to do is extract one column with the date ,one column with the patient forename and one with the patient surname.
I have tried various combinations of =RIGHT(A1,LEN(A1)-FIND(".",A1)) etc etc but cannot extract the data I need
I have some user data that is being imported into excel into a single cell and I need to be able to check in the cell for one of three different words and only pull those words out....
View 9 Replies View RelatedI need a nudge in the right direction with how to amend the below code so that it :
1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents)
3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)
The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.
I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007
Sub InsertComment()
'www.contextures.comxlcomments03.html
Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String
On Error Resume Next
Set rngList = Range("A1:A5")
strPic = "C:/Users/jeff/Documents/Standards/"
On Error Resume Next
For Each c In rngList
With c.Offset(0, 1)...................