I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula = SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.
Range("B7:B257").Find(What:="Reservoir").Activate
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
I have 10 worksheets in my workbook. The two that I'm concerned with in this post are "Main" and "Completed".
If Column AA in "Main" contains the word "Complete" (which is from a drop down list) I want to copy the entire row from "Main" and paste in the "Complete" sheet without over-writting previous pasted rows.
Back in sheet "Main" I want the data in that row to be cleared with the exception of Column A as it contains a formula for sorting blanks. If I delete that row it will mess up my links on other sheets.
How can I create a formula to add up how many cells in a column show a particular set of text? For instance; the column in my spreadsheet looks something like this:
MJR
DML
MJR
RJG
RJG
MJR
DML
I want to know how I can create a formula that will automatically tell me how many times "MJR" is listed; how many times "DML" is listed, and so on.
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx
I have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell.
Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19.
fig 1
Coloms A B C D
Row A 5 5 6 6
Row B 5 6 6 8
Row C 4 5 6 7
Row D 5 4 4 5
Total 19 20 23 26
fig 2
Coloms A B C D
Row A 1:00 2:00 3:11 4:01
Row B
Row C
Row E
Row F 1:19 2:20 3:33 4:27
Row F-A= 19 20 23 26
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
I have a database in excel, here's some sample data.
When they click the print button in Cell A2, up comes my form
When the print tags button is pressed, my secondary sheet is populated with the cell they clicked on
Now it's using a font, calibri 11, which isn't proportional but I was wondering if there's a way in VBA to "auto" detect the max width and place words on the next row accordingly (like a word wrap). Here's what I need it to look like:
Here's my existing code to place the comments as is:
Code:
entireComment = "Comments: " & Range("P" & i)
.Range("A" & cnt + 11).Value = Mid(entireComment, 1, 33)
If Len(entireComment) >= 36 Then
[Code]....
I've been trying to come up with a conditional that will format a row if a cell contains any instance of a given word.
I know the following will format rows containing the EXACT entry of "apple" in column E:
=$E1="apple"
But I'd like a conditional that will find entries like "apple pie" and "crabapple" as well. I'm sure it's a simple solution, using a SEARCH function or something...but I can't figure it out.
I have a spreadsheet with 12 coloums. In the last coloum are the ords "yes", "no", or is empty (null?). I need to find a way to have each row that contains the word "no" or is empty to be highlighted in red font and bold type. When the word "yes" is typed then the font is black and the type in normal. This allows me to see at a glance what orders have not been picked up or have problems.
View 2 Replies View RelatedI want to delete if my sheet have a value miles away
Sample
A1=0 miles away
a5=25 miles away
a30=50 miles away
how i delete these
a1=""
a5=""
a30=""
"' means null
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string
Set Finder = Cells.Find(What:="Account",...............
'Problem here
'
If Finder is not found then skip till next part of macro.
If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:
Col A
Col B
Col C
Col D
Col E
Col F
Code
Description
Cost
Rate
Mobile
0.13
1234
Australia Mobile
0.12
Not Mobile
[Code] ........
I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).
Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
I have two different functions, first is importing website to excel and the a second is testing string according to pattern. Each one of them is working ok. I'm trying to find a word " finance" in URL and put it into cell "A1".
Sub ParseWebsite()
Application.DisplayAlerts = False
On Error Resume Next
For i = 1 To 10
SiteURL = "URL;http://www.cnn.com"
With ActiveSheet.QueryTables.Add(Connection:=SiteURL, Destination:=Range("A" & i))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False................
I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.
View 5 Replies View RelatedI have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
I am struggling with a formula, that will display a certain cell only if a match is found, else produce a sum. It will be easier to look inside the document rather than explaining it on here. Described as well as I can inside the document
(None of the data used is factual. I created it myself to replicate)
Overtime for Payroll.xls
I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see.
View 4 Replies View RelatedI have been trying to create a macro vba that will look at a specific row in the current sheet and if it sees a name I need to delete that row. But I am looking to also under stand what it is doing so I can use this script in other instances. I just bought some things off MrExcel's Store but i won't get this for a few days.
View 9 Replies View RelatedI have been trying to write VBA which basicly search for specific value in column A and copy values from that cell to down and two columns next. Select row and columns down after specific value in column A was find.
Like this
A B C
... ... ...
X 100 115
... ... ...
up to last row
I try with VBA below but it do not works.
Sub Select_Rows_GK()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i).Value = "9000" Then
Range("A" & Rows.Count).Offset(0, 1).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
Exit For
End If
Next i
End Sub
My issue concerns going down rows in a data set. Due to the fact that the data set I am pulling from is not standardized, and roughly 10% of the data set has two extra rows of extraneous data below the reference cell (foreign currency balances which I don't need). The point in my formula is the "+2" (down two rows) condition. I needed it so that the "+2" is replaced with a condition inwhich after the reference cell in the INDEX( and MATCH( function is found the command goes down rows until the term "USD" is found. My formula is as follows:
{=IFERROR(INDEX('SHEET1'!$A$9:$O$10000,MATCH((RIGHT(C7,8)),(LEFT('SHEET1'!$A$9:$A$10000,8)),0)+2,11),INDEX('SHEET1'!$A$9:$O$10000,MATCH((RIGHT(C8,8)),(LEFT('SHEET1'!$A$9:$A$10000,8)),0)+2,11))}
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
I have a simple macro that copies data from worksheet to another. I only want this macro to be run once per day by the user. One of the cells that gets copied is the value found in the formula "=today()". My thought is that the macro could look for the existance of the current date in the pasted data (meaning the macro has already been run once today), and if the date = today, then a message pops up warning the user that the macro has already been run once today, and ask if they're sure they want to continue.
Any easy way to accomplish what I'm attempting to do? The current date gets pasted into a worksheet name "PriorDay" in cell C5.
I have a spreadsheet that lists all the work done by employees within a specific area. Some employees cover multiple areas.
I am now needing to work out the average work completed by each area. I need a formula that will count the number of employees by each work area.
I know this is something I have done before, but my mind has gone blank and I can't for the life of me work it out again (it's one of those days).
Row 3 of the spreadsheet contains the codes for the work areas they cover (CM, V & TC) and some employees only cover one (which would be a simple CountIf) but some have multiple.
What is the formula to, for example, count the number of people who have CM in row 3 even if they also have other entries in that cell.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View Related
