I noted today that in the helpfile topic for the IF() worksheet function it is possible to omit both the function's 2nd and 3rd arguments.
Although to do so you have to add a comma after the logical_test, eg:
=IF(A1="hello",)
When both [value_if_true] and [value_if_false] arguments are omitted the function always returns 0 if the condition is met or FALSE() if the condition is not met (unless the condition references an error value in which case an error is returned).
Note - I'm comfortable with leaving one of these arguments out, just can't think of a valid application for leaving both out. Just a feeling in the back of my mind that the fact that IF() in this format is able to return different data types (number vs logical depending on condition=TRUE vs FALSE) might mean that there could be a clever use for it?
Always have problems getting my head round the syntax of the indirect function and am unable to find anything similar that's been asked.
I want to perform an operation on two numbers where the user selects which to use (add, subtract, multiply or divide) entered into another cell like this:
******** ******************** ************************************************************************>Microsoft Excel - 200701 - LCC.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC3C5= ABCD14+5*2****3Normal*9*4****5Indirect*#REF!*Sheet3* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is
Function INTSPOT(spots, year) 'Interpolates spot rates to year Dim i As Integer, spotnum As Integer spotnum = spots.Rows.Count If Application.WorksheetFunction.Count(spots) = 1 Then 'Single rate given INTSPOT = spots Else 'Term structure given If year = spots(spotnum, 1) Then INTSPOT = spots(spotnum, 2) Else Do i = i + 1 Loop Until spots(i, 1) > year INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _ (year - spots(i - 1, 1)) / _ (spots(i, 1) - spots(i - 1, 1)) End If End If
I have a spreadsheet that deals with payments and has subtotals in it. Every subtotal formula begins like this: =SUBTOTALS(9,H212:H225). My question is: What does the "9" and the "comma" mean?
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta() Set wba = ActiveWorkbook On Error Resume Next If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt" Else Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls" End If Set wb = Workbooks("Projekt.xls") wb.Activate If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then MsgBox "overall doesn't exist!" Else........................................
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Im having problem with If/Then/Else Statements Not sure what the problem is. I have a text box a user inputs whatever in to create two new tabs one is the tab name then the next is tab completed. It isnt liking my syntax I have here.
I have the following code that loops thru and puts the word 'TEST' in column J if column B has a TEXT value of '020'
---------------------------------------------------------------------------------- With Range("J1:J" & Lastrow) .Clear Range(.Cells(2), .Cells(.Count)).Formula = "=if((b2)=""020"",""TEST"","""")" End With ----------------------------------------------------------------------------------
Questions 1) why do I need double quotes "" "" on every argument in the function? Is it because I'm working with TEXT data? 2)I want to use multiple ifs (ie if cell b2 equals '020' OR '030' then put the word 'TEST' in col J). How to change the syntax to do this?
I have a form and a combo box that I want ot fill the items in from cell F22.
Here is my Sheets(PowerAnalysis.xls).Cells("F22").Value = ComboBox2.Text What is the best way to get this done? I have searched here and on the web and I can't seem to find any straightforward answers.
I am trying to get the below autofilter working but to no avail, its just putting the text datStart and datEnd in the auto filter, not what I receive in the boxes, see bold line for line in question
Dim datStart, datEnd As Date Dim strAgent As String datStart = txtStart.Value datEnd = txtEnd.Value strAgent = txtAgent.Text Range("A4").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:=">datStart", Operator:=xlAnd, _ Criteria2:="<datEnd"
It doesn't work any more in Excel 2003. Of course it works directly such as: "WHERE (dbase_file.field1=' N0011') but I couldn't find the way to replace the ' N0011' with the parameter.
It is not the singular issue... "FROM dbase.file dbase.file " that worked well in Excel 2000, the Excel 2003 "wants": "FROM 'drivepath'dbase_file dbase_file " and I couldn' find a way to replace the explicit 'drivepath' with a predefined variable.
I am attempting to create a macro that opens a file named "shrinkage-billing.xls", searches for a variable "PTOSH" in column A, copies the adjacent cell and pastes the data in another worksheet named "Shrinkage Report 2009.xls"
Got a wee problem with some syntax for a copy function. At the moment the code i have copies the range and pastes to another range which is defined by a cell number. But i need it to paste special (values only).
I would like to know where can I find a good resource to learn about VBA syntax. I have VBA books and there are some examples of code in there but when I'm trying to read someoneelse's code I just get into commands or syntax I don't understand. For example I would like to be able to go some site and decifer what this line (or it's parts) means: If(cnt < MAXTEST, sDigSep & String(MAXTEST - cnt, "9"), "")
I have a variable array, that is, the first cell of the array is variable and the last cell is variable. I have dimmed the first cell , "firstcell" as a range. I have dimmed the last cell , "lastcell" as a range. I'd like to sort the array but first I have to select all cells in the array. Need the proper syntax to select all cells between "firstcell" and "lastcell" in my macro.
