How do you go about entering the word "ZERO" in an IF statement? Every time i try to add it to my formula it turns it into the number 0 which is not what i want because i want to differentiate between the two??
View 1 RepliesHow can I write an IF statement that evaluates whether a cell's value is
BETWEEN two numbers?
Example:
A1 = 89.99
I need a statement that evaluates whether A1's contents are between 80.01
and 90.00.
My colleagues produce a monthly MI pack which includes Excel spreadsheets they produce, spreadsheets others supply to them, and commentaries in Word. All the disparate sheets makes it hard to get page numbering right. They also want to be able to send the pack out electronically, and lots of individual files obviously isn't ideal for this.
The last time I had to do a similar thing was several years ago, and I used Binder, which wasn't great, but was better than nothing. But it seems to have been discontinued. Has anyone got a neat solution?
I have a large range of data and on each row the cells contain either 0, 1, No or another word. I would like to display the other word at the start but im really stuck on how to do it. This other word can change on each row but each specific row will only have one word. I can post a sample spreadsheet if necessary.
View 5 Replies View RelatedI have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI 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.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
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 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 RelatedI am in the middle of automating a process here at work, the program takes a word, "pencil" for example, from excel. It will then open up a word document with content already in it (premade template). The program will then find all instances of a string, "placepencilhere" for example, and will replace that instance with the string from excel.
Basically I want to be able to take a variable that has a stored string value from excel and use it to replace another variable in a word document.
I tried recording a replace (ctrl+f, replace tab) macro, copying the code, and inserting it into the excel vba code.. but I get a error message. Here is what I have:
---
I have a bunch of cells in column b that have products.
Column B
Dell 24" lcd
vaio sony laptop
8.0 mpxl kodak camera
photoepsonprinter
Basically in Column A I want a formula that'll say.
If the word "dell" is somewhere in cell b1, then put the word "Dell" in cell a1.
If the word "sony" is somewhere in cell b1, then put the word "sony" in cell a1.
And then so on and so forth down through column A. The brand names are potentially endless, so is there a easy way to on like a seperate sheet make a list of brand names and have it pull from there?
Originally I made a if function that said if the brand name was contained in the cell then output the brand name, but that caps at 7 for the amount of brands i can use....
I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.
So far I have managed to get it all working EXCEPT if I enter 0 as a value.
For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered.
(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.) ...
01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
I'm running the subtotal function, but for some reason Excel is including first row of next group in one of the groups. The label I am subtotalling on is the result of a formula, but why would that make a difference?
See row 11 and 12 in the attachment : subtotal problem.xlsm‎
i can do the very very simple math formulae in excel, but now i have two excel files. Suppose the first excel file contains the numbers: x1 x2 x3, and that the second contains the numbers: y1 y2 y3.
i need to make a formula that includes x1 value and y1 value, then another one that includes x1 and y2, then x1 and y3, then x2 and y1... and so on.
I have a row of cells containing numbers, some of which are 0, how can i get an average where it averages all cells except the 0, as at the moment it is distorting my results.
View 3 Replies View RelatedI am using a formula to include all values greater than zero in my average which has worked fine until i tried to extend the parameters, then it gives me the Value? sign. Does anyone know another way to accomplish what i want?
here is what i am using now.
=average(if(BZ28:CM28<>0, BZ28:CM28, " "))
i was trying to make the CM extend to CS, but for some reason it is not working.
Anyway, i figure a different equation might be better than what i
have and solve my problem.
I have entered the following formula to add up a list of data. =COUNTIF(B7:B100,"*") This doesn't include gaps with blank cells and only gives me the total number of cells that contain text. However, some of the text is duplicated and I only want to count the total number of unique entries in the list.
View 6 Replies View RelatedI have a workbook with numerous worksheets. Each worksheet is similar to all the others, and I want to be able to (for example) lookup the values in cell C10 for each worksheet and record the value in a summary worksheet.
The name of each worksheet is in Row 1. Is there a way to reference the sheet name using the values in row 1?
I’m trying to save a new workbook with the current date included at the end of the filename, in the format yyyy-mm-dd.
I took a guess at the following but it doesn’t work - the filename comes out as 'Report39163' Can anyone help please?
ActiveWorkbook.SaveAs Filename:="\UsersDataTemp" & "Report" & Format(Date, yyyy-mm-dd) & ".xls")
How do I count the number of air handling units, "AHU" column without counting the duplicates........
View 9 Replies View RelatedHow do I modify my formula (below) to include an OR statement?
=SUMIF(I4:I20,"Core",G4:G20)
I need something like this:
=SUMIF(I4:I20,"Core" or "Existing",G4:G20)
I want a macro to sort Column A through B (Sort newest to oldest)that includes the first row. I can record a macro but it doesn't include row 1.
View 9 Replies View RelatedI have 3 columns of information. Column A will be between 1-6, Column B is between 1-10, Column C is the results that I need averaged. I need the formula to give me the average of Column C of all 1's in Column B that are even numbers in Column A. I have been looking on forums, and can find multiple criteria for ands, but can't see where I can do an and /or. I could do Column B = 1, Column A =2, but I need it to be OR =4 OR =6. I don't see any easy answer in excel for even/odds.
View 8 Replies View RelatedI want to create a 'capture all' formula that adds up cells in a column This column may contain blank cells in one column but when copied to another column, the same row may contain a value.
example I want the sum of A5 + A8 + A10 + A22 but in column A cell A5 may be blank and so may A22 but when the formula is copied to column B all the cells may contain a value
When I try to do it I receive a Value# error for columns containing blank cells I don't want to do a separate formula for each column