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'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.
I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.
Code: Dim aWB As Workbook Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value
The following sub to create and name wsheets results naming the new sheet with the value of "A9" only, but what I want to name the new sheet is "A9" + "B9". Sub CreateWorksheets() Dim newSheet As Worksheet, itemSheet As Worksheet Dim cell As Object Dim itemrange As String
Set itemSheet = Sheets("BIDFORM")
Application.ScreenUpdating = False itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address For Each cell In itemSheet.Range(itemrange) If SheetExists(cell.Value) = False Then Sheets.Add Before:=Sheets("BACK SHEET"), _ Type:="C:PathFile" Set newSheet = ActiveShee newSheet.Name = cell.Value End If Next cell Application.ScreenUpdating = True End Sub
I know that I cannot automatically assign UniqueCount the value of that formula. How would I format the line such that I can indeed assign UniqueCount the row value of that line?
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
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 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?
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?
I am having problems with the following formular in that it does not return what I am expecting.
I have 2 columns of data that hold Y/N values and I would like either "Yes" or "No" to be displayed in Col C for the 4 possible combinations that the interaction of Y and N in columns A&B give.
The data is Col A:A = Y or N Col B:B =Y or N
The four possible combinations and outcomes are: Col A = Y & Col B = Y outcome in Col C = "Yes" Col A = N & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = N outcome in Col C = "No" Col A = Y & Col B = N outcome in Col C = "No"
The formular I have tried is: =IF(OR(AND(A1="Y",B1="Y"),A1="N",B1="Y"),"Yes","No")
However while three of the outcomes for the combinations are correct (N/Y, Y/N, Y/Y), the output for the N/N combination is incorrect and returns "Yes" instead of "No".
Just wondering if anyone has any ideas as to what is wrong with this?
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut() Dim NewName As String Dim nm As Name Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _ "New sheets will be pasted as values, named ranges removed" _ , vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application .ScreenUpdating = False
On Error GoTo ErrCatcher Sheets(Array("Copy Me", "Copy Me2")).Copy On Error GoTo 0 For Each ws In ActiveWorkbook.Worksheets ws.Cells.Copy ws.[A1].PasteSpecial Paste:=xlValues....................
I have two pieces of code in module 1 and module 2 respectively:
This code is meant to find the first row number that has a string value of "" in column A:
HTML Code: Sub Macro1() Dim cell As Range Dim r As Double Dim p As String For Each cell In Worksheets("stock in").Range("stockcode") p = cell.Value r = cell.Row If p = "" Then Exit For Else Next cell End Sub
BTW column A will contain formulas that evaluate to "" so I assume cell.value will = "" even if it contains a formula?
In the second module a sort is executed on the range based on the number of rows:
HTML Code: Sub Macro2() ' ' Macro2 Macro ' Call Macro1
My code is falling down because I think my variables arent scoped right or my code is in the wrong place. Also macro1 will eventually be executed from a button on a different sheet (I dont know if this will matter).
The whole point of this code is to get around the fact that if you perform A - Z sorts on cells that contain formulas those that evaluate to "" will be at the top leaving lots of blank rows before actual visible data.
I have a line of code that compares cell values with a variable.
Code: dim rmycell as range If rmycell.value = MyTarget then
This works fine when rmycell.value is empty or has a value. However, sometimes rmycell has a #VALUE! Error. When debugging the code I can see this error showing rymcell with a value of "Error 2015". The MyTarget variable is "abcdefg", so the If statement should resolve as
Code: If rmycell.value = MyTarget then becomes Code: If Error 2015 = "Abcdefg" then
Which should be a negative result. Except it isn't. The code that is conditional on a positive result runs whenever there is a #VALUE error, just as if the 2 values were equal.
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1Container ID26 11 135 8 11 245 7 11 355 7 565 6 9 11 675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
CTRL/END key combination should goto and select the last cell in a Worksheet. So for a Worksheet using Rows 1-4135 and Columns A-Z it should indicate Z4135 as the last cell My current worksheet uses these Rows and Columns, but CTRL/END indicates last cell as being AB4506. (Inflated by 371 Rows and 2 Columns)
Deleting the unwanted Rows or Columns, or "Clearing contents" of them does not affect the result given by CTRL/END which still shows AB4506.
How can I adjust/correct the last cell indication?
Usual work-around is to select the wanted portion of the worksheet and copy it to a new blank worksheet, but is there an easier way?
I have a userform on which there is a frame containing 8 option boxes. After a query, the results are displayed in the form. If I have a value of 1 in a cell, optionbox1 is checked; a value of 2 checks optionbox2, etc. It works great EXCEPT when first initialized. At that point, it checks the last optionbox, even if the number is 1. I have built a next and previous feature to scan the data, and when I return to the first entry, the correct box is checked. I tried coding blanks into the fields prior to populating them, but I still get the same results. Is there some explanation available so that I may remedy this? I'd really like the first piece of data to be correct.
I have a text file which have a column with data like xxx . however, when I open the file with excel and it displayed incorrect value like xx.xxx, x.xxx
I tried to change the format cell to text, but it still cannot display original data...
Usually when you try to open a file that someone else is using, it lets you know the file is in use, and lets you know who is using it. However, the situation I have is that when a user tries to open the file that is in use, it tells them that the file is in use, but gives them the wrong user who is using it!
For example, User1 opens the file and closes it again. User2 comes along and opens the file and stays in the file. User3 comes along and tries to open the file, but instead of saying User2 is using the file. It says User1 is using!
The file we have was originally built in Excel 97, but we using it through compatibility mode in Office 2007. The file is needed by various users, but putting it on shared access is not an option.
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way.
On the PROPOSAL tab, at I41, a simple multiplication instruction is wrong. It's multiplying 72*186.53 which = 13430.16 in the real world, but on this sheet 11 cents are missing. I'm pulling my hair out on this one....
