Different Results Requested From The Same UDF
May 30, 2009
Is there a "simple" way to "kill two birds with one shot"!? In the attached WB I wrote a small UDF in order to Multiply A1*B1 whenever it is used in a cell at an ODD row number, - and Add A1+B1 when used in a cell at an EVEN row number. As far as I know it was given as an exercise to a class of students in a European University.
Upon opening the WB - the UDF presents the correct results, BUT when A1, or B1 is changed the results get corrupted. If you press [F2] and hit [Enter] in each colored cell - the results get back into order. My question to you is very simple:
a) Where did I go wrong ?
b) Is there a different UDF that can provide the requested results and being Volatile at the same time ?
View 4 Replies
ADVERTISEMENT
Nov 29, 2011
I receive the following error whenever I try to run a macro in Excel: "System Error &H80040111 (-2147221231). classFactory cannot supply requested class." This is immediately followed by "Out of memory"
I was eventually able to stop the error from occurring by removing all forms containing a listview control in my macro file.
I have tried repairing my Office installation, uninstalling and reinstalling Office, and reregistering MSCOMCT2.OCX but nothing seems to work.
View 9 Replies
View Related
Feb 23, 2010
I'm trying to build a hyperlink using text formulas in Excel but I get the following error message: "Unable to open [link]. The internet site reports that the item you requested could not be found. (HTTP/1.0 404)". The strange thing is that if I copy-paste the link in the web browser, the page opens normally (which proves the link is correct).
View 4 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
May 9, 2006
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
View 9 Replies
View Related
Jan 2, 2013
I have a file with the following code. When the file is used on a PC, the code works properly and the result is Print Preview. When used on a Mac, the print job goes to the printer rather than to Print Preview. Can this code be adjusted so that it works on both a Mac and a PC?
Sub PrintEstimate()
Sheet1.Names("Print_Area").RefersTo = Sheet1.Names("Print_Area2").RefersTo
Sheet1.PrintOut Preview:=True
End Sub
Sub PrintBoth()
[Code] .......
View 1 Replies
View Related
Jan 28, 2009
I would like a formula that looks for Y and results Y. See two examples below.
Example
Cell B2 = Y
Cell D2 = N
Cell E2 = Y
Cell F2 = Y
Example
Cell B3 = N
Cell D3 = N
Cell E3 = N
Cell F3 = N
View 4 Replies
View Related
Sep 4, 2009
What is wrong with this formula? It results in #Value error.
View 3 Replies
View Related
Mar 14, 2014
I have a workbook that calculates the total sales per agent but looking a datasheet. At the bottom of the sheet I have 4 team managers who look after a group of agents. I need their totals to me calculated by summing each of the agents in the teams.
I have tried to create groups per team_manager using my team_ref_sheet. On this sheet I have Defined Names / Named Ranges which I thought would work for doing these calculations, but this does not seem to have worked.
View 5 Replies
View Related
Aug 20, 2014
I have a macro which returns the results for
=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))
As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?
Before it was:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"
I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"
as I would in Excel, but of course it doesn't work.
View 3 Replies
View Related
Aug 27, 2008
There is a machine in our office that is running the same software as my machine. (XP SP3, Office 2k7, All MS Updates)
On my machine, as well as most others in the office, all the code works fine. On another machine, strange issues arise.
View 9 Replies
View Related
Feb 2, 2009
I have a monthly work phone bill which I receive as an Excel document. I have to pay for personal calls and want to create a list of personal numbers which can be filtered and the costs summed. Prefereably an add in would be best. Possibly 2 icons, one for adding my personalised numbers (usually under 10 numbers). This could be saved to my home drive on the network so I can run the formula from any PC on the network.
The other icon would be to run the report and do the calculations. So it only really looks in 2 column (mobile num, cost).
View 14 Replies
View Related
Jul 24, 2009
For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):
View 3 Replies
View Related
Mar 4, 2009
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)
View 11 Replies
View Related
Jan 9, 2007
what was the anser to a simple question earlier has now got me thinking...
I have one mega formula (see below) that based on a set of criteria returns a result from a range in an accompanying workbook:
=INDEX('input plus'!D940:D979,MATCH(1,('input plus'!$DJ$940:$DJ$979>=2)*('input plus'!$DG$940:$DG$979=1),0))
(the range 940:979 is a filled with investment funds data all within a particular sector, this formula for example will return the name of the number 1 rated fund, ie "mega star cash")
However, the above assumes that the ranges specified wont change, but they do, frequently and there are over 300 of them, so I earlier got help to create the following formula which can tell me what range a certain set of data lies within:
=CONCATENATE(ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,0)+0,4,4,1),":",ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,1)+0,4,4,1))
(so I tell this formula to look at a specified sector name 'cash funds' and by tweaking the number in bold it will return the range of data that i want to feed into the first formula, ie it will return D940:D979, DJ940:DJ979 and DG940:DG979)
If the data ranges change then these cells will dynamicaly update, either the range will shrink or grow - which is perfect - what i want to know is if i can integrate this into the first formula, so if you like it becomes completely dynamic.
View 9 Replies
View Related
Jul 20, 2007
I have made this UDF to narrow down results to more suit my selling environment. In the NHS UK there are many job roles but for me I need to know if they are a doctor (GP), nurse (Nurse), Pharmacist (Retail). There is some broadness in the NHS and it's workings but for me I just to know what general category they fit into.
Problem I have is that when I use this my spreadie takes about 10 minutes to update (I can be looking at 15000 records at anyone time). Is there anyway of speeding it up or should I just percivier?
Function PHARbase(cell)
Select Case cell
Case "General Practitioner"
PHARbase = "GP"
Case "CLINICAL ASSISTANT"
PHARbase = "GP"
Case "GP Registrar"
View 9 Replies
View Related
Dec 31, 2008
I have created a simple SUM formula to add cell values across six sheets in the same workbook. I see the result when I use the formula button but the result (number) does not appear in the cell. All I get is $ -.
View 9 Replies
View Related
Feb 20, 2009
I am trying to do a small survey within my company by email.
It is very short, 5 questions, I was hoping to add in yes/no buttons and have a code that will sum up the individual responses.
Something like (yes(1,4)=2) (No(2,3,5,)=3 .......
View 9 Replies
View Related
Oct 11, 2006
I wan to create a table using 2 column of data.Below are the data
Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim
I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.
View 4 Replies
View Related
Jan 7, 2007
attached the file. Sheet1 & Sheet2 are the "Source" sheets and "Summary" sheet is the "Target" sheet.
1) Macro "TopParts1" filters the Top 80% (Column B) in Sheet1 and copies this to A1 in the "Summary" sheet.
2) Macro "TopParts2" filters the Top 80% (Column B) in Sheet2 and copies this to E1 in the "Summary" sheet.
What I am hoping for is to combine the macros 1) & 2) so that the Table as
shown in "Summary2" sheet is done in one instance. The highlighting is just for illustration. Presently I need to select sheet1 rum the 1st macro... select sheet2 run the 2nd macro. Not very efficient. One table for 1 macro would be excellent.
View 5 Replies
View Related
Aug 30, 2007
I have a sheet with Product IDs and amounts. A Product ID may occur more than once. I want to sum all amounts for all occurrences of each Product ID.
(see attached file)
I have written the following code, but as you can see, it is returning an error, so I've done something wrong. I'm also getting a Compile error: "For Each control variable must be Variant or Object"
Function GetTotal(PO_ProdID As Range, LURng As Range)
'
' Function to Lookup Concatenated PO # & ProdID
' & Return the Total of all dollar amount listed
'
Dim ce As String, tot As Long
tot = 0
For Each ce In LURng
tot = tot + ce.Offset(0, 1).Value + ce.Offset(0, 2).Value
Next ce
End Function
View 4 Replies
View Related
Jul 6, 2013
I have a large table which I am filtering 3 times. There should be results but the final VBA filter does not return them until I manually apply the filter. The custom filter is correctly populated with the criteria. The code that I am using is as follows:
VB:
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="=INV", Operator:=xlOr, Criteria2:="=CRD"
Selection.AutoFilter Field:=21, Criteria1:=">=" & DTPicker1.Value, Operator:=xlAnd
The first two filters work fine. The last does not. Oddly it works on some date picker values and not others, even if there are actual results and as mentioned earlier if I stop the program running and apply the filter (without typing anything else) it works fine.
View 5 Replies
View Related
Apr 8, 2014
I would like to be able to bring these results into an output, but without other members of the other team members results been seen by other memebrs of the team
View 7 Replies
View Related
Apr 9, 2014
Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.
The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.
VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.
=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))
View 3 Replies
View Related
Apr 22, 2014
I attached my example with this message, I want the cell Z6 for instance to make the sum of the "X" of the same record based on their "weight" in the gray ribbon respectively.
View 3 Replies
View Related
Jul 8, 2014
Example Data...
----A---B---C
1---B---1---
2---A---2---
3---E---5---
4---C---4---
5---D---6---
6---G---3---
7---F---6---
I have this formula in cell C1, filled down to C7:
=IFERROR(INDEX($A$1:$A$7,SMALL(IF($B$1:$B$7>=5,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(C$1:C1))),"")
This creates a list of cells from column A where the correspsonding value in Column B is greater than or equal to 5.
RESULTS:
----A---B---C
1---B---1---E
2---A---2---D
3---E---5---F
4---C---4---
5---D---6---
6---G---3---
7---F---6---
Any way that would generate these cells in alphabetical order making it look like this:
----A---B---C
1---B---1---D
2---A---2---E
3---E---5---F
4---C---4---
5---D---6---
6---G---3---
7---F---6---
Jason.CellsThatMeetConditions.xlsx
View 8 Replies
View Related
Jul 22, 2014
Transferring Multiple Data from separate worksheet without duplicating"
As you can see i am looking for information in column d (Letter) and getting the result to show from column B (number) using the formula in Column G (result 1).
The problem i am having is that if i try to copy or drag the formula down into Result 2 i just get the same answer.
Is there any way for the next cell down to ignore the previous result?
View 14 Replies
View Related
Jul 29, 2014
why is that I have the same time value but with different conversion results?
I have values in column A10:A26 with an average time of 00:00:38 result with converted value of 7052.04.
When I type 00:00:38 in Column A30, it gives me a result of 7139.37...
Why do I have different conversion results knowing that I have the same time of 00:00:38?
I Have attached a sample workbook.
View 14 Replies
View Related
Dec 10, 2013
Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3
New KS4 grades Markbook.xlsx
View 5 Replies
View Related
Feb 21, 2014
I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.
=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1)
I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.
View 2 Replies
View Related
