Function Works In VBA But Does Not Work Inside Worksheets
Jun 21, 2008
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error
The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.
I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.
PHP
Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double Dim I As Integer, Slope As Double, NData As Integer NData = UBound(X) For I = 1 To UBound(X) - 1 If (X(I) = X0) Then Interpolate = Y(I) Exit Function ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1)) Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1)) Exit Function End If Next I End FunctionPublic Function ListMax(ParamArray ListItems() As Variant) Dim I As Integer ListMax = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) > ListMax Then ListMax = ListItems(I) Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant) Dim I As Integer ListMin = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) < ListMin Then ListMin = ListItems(I) Next IEnd Function
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.
Sub ArrangeColumns() ' ' ArrangeColumns Macro ' Macro recorded 3/7/2008 by **** '
So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.
I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.
I have a worksheet which holds a concatenation of AppID's and App Names in Column U. There are approximately 12,000 rows and each cell in Column U holds one of either of the following value formats:
I need to find all instances of a chosen App ID and then copy any row in which the App ID appears to a new sheet (to obtain the chosen AppID I am presenting a list of those to the user in a form Listbox, and I know the selection ofthe AppID is functioning as I am currently presenting it in a MsgBox prior to running this part of the code).
When I run a manual FindAll on a given AppID it returns all the cells in Column U which that AppID appears, but when I use the following code to achieve the same it does not seem to find the AppID's.
(NB - I've "borrowed" this code from a posting on Ozgrid, but I have also compared it to the many other FindAll methods available on the web and they all apppear to be pretty similar).
(The changing of the cells interior colour is just a way of identifying whether it's working prior to writing the code to copy the row ino a new sheet).
Dim temp2WS as Worksheet Set temp2WS = ThisWorkBook.Worksheets("AppID") Dim lCount As Long Dim rFoundCell As Range Set rFoundCell = temp2WS.Range("U1") temp2WS.Activate
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row For i = 2 To LastRow Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)" If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _ ((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then Worksheets("Sheet1").Range("AE" & i).Font.Color = 255 End If Next i
My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.
Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
I do a payroll workbook for my small company. Each sheet inside the workbook is a month. Each sheet totals the monthly labor categories. I would like to keep a "year to date" total on each employee and therefore would have to reference each sheet.
Setting up a formula (or tell me where to look) on how to total worksheets inside a workbook?
Example: the workbook contains a total of 12 sheets, representing the 12 months of the year. Add "cell reference" from sheet 1 to sheet 2 to sheet 3 to reach a total, then when sheet 4 is completed it contains the cumulative totals of all 4 sheets, etc., etc. Where I can look at this "year to date total" and compare to last years "year to date total".
I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).
I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".
I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.
I have attached a sample of the offending spreadsheet. The rounding function is in column "B"
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8 Cell D2 contains a pre-calculated number (e.g. 53.012) Cell F2 is the cell that I need an IF function or something similar – the function would be as follows =IF(B2=1,D2*1.000) =IF(B2=2,D2*1.00057) =IF(B2=3,D2*1.00171) =IF(B2=4,D2*1.002281) =IF(B2=5,D2*1.003421) =IF(B2=6,D2*1.003991) =IF(B2=7,D2*1.004215) =IF(B2=8,D2*1.004538)
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel: -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(-1)*7 -6--7 -6+7=1
But in VBA you get -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(0)*7 -6-0=-6
If you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:
Sub roundingtest() Dim a As single Dim b As single Dim cases As Integer a = 18200 b = 0.01 cases = Int(a * b) Debug.Print cases; End Sub
this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?
Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so 182.1 -> 183 182.9 ->183 182 ->182
So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!
I have a column, where i want to calculate the difference between today and another date/or viceversa. The problem is, that in the first two cells, it calculates it well, but then, it shows #VALUE and when I press to see the "calculations steps" , it shows "29/09/2009"-40049. It turns the today function into a number. Why?
This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.
The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.
I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.
I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I'm trying to calc a Simple Moving Average of financial Fund data (row=fund type, column=date) on a very large matrix for a particular fund and date range. I locate the Fund of interest and retrieve the Start and End dates of interest and retrieve their addresses using the ADDRESS function. Since ADDRESS returns a text string, HOW DO I incorporate it's results into the following array function (which ignors blanks and 0 cells): {=AVERAGE(IF($L$10:$V$10>0,$L$10:$V$10,FALSE))}?
This is the format of an external call that I am trying to run. I need to change the number (40720) based on the day of the month (i.e. if its the 5th, then use 40725.
I am attemting to use a function to do this and passing in the device name (Windham) and location (40720). Unfortunately, I have been unable to get a String to send back as a formula. My attempt was as follows :
= Location ("Windham", 40720, "D7")
Function Location(name, num, cell) Dim psDay As Integer Dim psLocal As String psDay = Day(Now) Location = (Range(cell).Formula = "=StacServer|" & name & "!'" & (num + psDay) & "'") End Function
I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.
Sub B_Test() Dim myRange Dim Results Dim Run As Long myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown)) Range("M3") = Application.WorksheetFunction.Sum(myRange) myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown)) Range("D3") = Application.WorksheetFunction.CountA(myRange) End Sub
I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.
I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.
inside cell a30 i have a date function which displays: Mon 07/22/2013 3:30:14 AM. i realize that excel actually uses 41477.146 Can i search for something kin the display, i.e. "Mon"?
Solution for all: {=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$16,$F$34:$F$40,INDEX(Sheet1!$B$2:$K$16,0,MATCH(T$10,Sheet1!$B$1:$K $1,0))),U34:U40)}
Say I have an Excel workbook with two sheets: Employee Hours and Employee Wages. On the Hours sheet I have 31 columns (Jan1 - Jan31) with inputs for the number of hours worked by each employee by day. On the Wages sheet I have listed the same 31 columns with their wages each day (I do this to factor in wage increases that occur fairly often).
My goal is to use an INDEX/MATCH function to calculate the total daily compensation for each day on the Wages sheet by multiplying hours*wages for all employees that day. Here's the code I'm using:
I'm starting a project where Excel will be used as the main UI for defining a table of data. I'm expecting to define a "template" - xlt i guess - that users can open and save as an xls, without over-writing the template. Users will populate the spreadsheet with a lot of help from user defined functions. I'm just getting started and would like to populate a particular cell with the current date, but only the first time the sheet is opened. I tried checking whether a cell was empty before assigning a value to it, of course this meant recursion!
Also: It might be nice to use the Today() function inside a UDF, but TODAY isn't a member of Application.WorksheetFunction - is there no way to reference TODAY() from within a UDF?
The first three formulas give valid results but the fourth example throws in an error after hitting Enter. As you can see I put in the HOUR function. As far as my knowledge goes the HOUR function generates an integer.
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.
My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments) If conditions Then nameOfMyFunction = "No GRP" ActiveCell.Font.Color = RGB(255,0,0) End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
Is it possible to use the HYPERLINK function inside of a validation list?
Presently I have a dynamically named range on another worksheet that includes a hyperlinked term. When I attempt to create a validation list using this named range the term appears in the list without the hyperlink.
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name Cell B - Monday Cell C - Tuesday Cell D - Wednesday etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
