w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
i am trying for a function for excel which is to sum up excel's data sheet
Below is the module I have come up with. Not much of VBA coder, But did the best I could, Some hardcoding is done like Sheet name i have assumed to be "Sheet1" and "Sheet2", You can change those as per your need, and neater nested IF can be used: Sub test() Dim hold_last_value As String 'To hold the value of code of previous cell for a given id Dim counter As Integer 'To hold the value of days count for which a given event was held continuously for a given id Dim from_date As Variant 'To hold the start date of the event Dim to_date As Variant 'To hold the end date of an event Dim row_writer_index As Integer 'To hold the value of the row to which the next output is to be written, Is passed as a ByRef argument to the fu

Categories : Excel

Excel equivalent Google Docs function to "query()" custom VBA function
Based on @chuff's approach, give your data array labels (I chose A>Z from A14:Z14) and name the array (A14:Zn) - I chose GParry. DATA > Get External Data - From Other Sources, From Microsoft Query, Databases, Excel Files*, OK. Select your workbook name, OK, Table: GParry, Add, Close. In the list under GParry, double-click on A, J and Z in turn, then click on the SQL button. In SQL statement: change SELECT to SELECT TOP 9 and append: WHERE J = 'WH' ORDER BY Z DESC OK, OK. If you want the results back into Excel just select the extract (top left), Edit, Copy and Paste where it suits you.

Categories : Excel

Sub function Excel VBA
Its the function-like parentheses it dislikes so either; cyberlinkcompute "A1","B2" or less advisably: call cyberlinkcompute("A1","B2")

Categories : Excel

Excel file created in Excel 2007 is not opening in Excel 2003 using c#
If you make an Excel file programmatically and name it with the .xls extension from Excel 2007 or 2010, you may be making an Open XML (.xlsx) file unknowingly, as this is the default save format for those versions of Excel. Essentially, if you are hard coding the file name, you may be saving an .xlsx file with the .xls extension, which would explain why you can't open the files. Try renaming the files generated from Excel 2007 to the .xlsx extension and see if you can open them.

Categories : C#

Display Excel inside VB6 / VB.NET Form & work with Excel without excel menu
As far as I can tell - this is not possible. The reason being that you cannot subclass the excel window in VBA. If you program outside of excel with VB.NET or VB6 it would be sort of possible, but would probably not work really good. If you only want to display some data from a sheet and perform some small actions on them, you could probably read the Data via the EPPLUS Library, display them as a datatable (EPPLUS can convert between datatable and excel files), perform your actions and save them to the file afterwards. But this would only work in VB.net

Categories : Vb.Net

Excel VBA Function Recalculation
You could try to add additionally Application.Volatile False at the very beginning, in the first row inside you function, like: Public Function MyTableResults(Code As String) As Variant Application.Volatile False '...your code here End Function However, it could help in some situation like inserting rows/columns but will not help when deleting rows (which I've tried).

Categories : Excel

Using the "Cells" function in excel vba
You're using the Range type like a function; the Cells call is actually returning you a Range object already. Instead of Range(Cells(1, (21 + itemcnt))).Select ..try: Cells(1, (21 + itemcnt)).Select If you want to be more explicit: Dim target As Range Set target = Cells(1, (21 + itemcnt)) target.Select

Categories : Excel

How to execute this Excel to XML function in a sub?
To convert to a Sub that could be run from a button you would change it to: Public Sub ExportToXML() This will automatically change the last line to End Sub. FullPath and RowName will no longer be passed as function-arguments, so would, presumably, need to be read from cells on a worksheet, or perhaps from two InputBoxes. The Sub would no longer return a Boolean value, so whatever happens with this value would have to be converted to code within the same Sub (or possibly passed to another Sub).

Categories : Vba

Using the IF function in Excel 2010
Assuming a data setup similar to this: In cell F2 and copied down is this formula: =IF(D3="",COUNTIF(B:B,">="&TIME(,,D2)),COUNTIFS(B:$B,">="&TIME(,,D2),B:B,"<"&TIME(,,D3))) You could then create a chart from the data in columns D:F. Does that work for you?

Categories : Excel

excel function INDIRECT()
The formula A:A returns the value in column A that's on the same row as the formula. So, if your formula is in B6, then INDIRECT($D$2&"!A:A") will return A6 from the sheet referenced in D2

Categories : Excel

Excel Nested IF Function with AND/OR
Doing a custom format for cells in column B should do the trick, with no coding / functions needed. Choose the cells you want to change, right click, go to format cells. From there choose the Number tab, and choose the Custom category. You could do something like "I"000000 in the box under "Type".

Categories : Excel

Excel 2007 VBA VLookup function
Sub Update() Dim rngNums As Range Dim cmpny As Range Dim v As Long Dim ws As Worksheet Dim result As Variant Dim c As Range, nm As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error GoTo haveError 'lookup range Set cmpny = Sheets("CustomerNumberList").Range("A1").CurrentRegion For Each ws In Sheets If ws.Name Like "*Sheet*" Then v = ws.Range("A" & Rows.Count).End(xlUp).Row 'set v = number of rows Set nm = ws.Rows(1).Find("cust_num", LookAt:=xlPart) If Not nm Is Nothing Then Set rngNums = ws.Range(nm.Offset(1, 0), ws.Cells(v, nm.Column)) For Each c In rngNums.Cells If c.Value <> "" Then '

Categories : Excel

Excel function: "Not Isblank" with "Or" and if date is
I can’t really test this due to how Excel localizes its function names (which is so stupid…), but you should be able to combine multiple booleans with AND or OR, depending on which behavior you want. If you want to check if two cells are empty you can use AND(ISBLANK(H4), ISBLANK(H5)) as the condition; if you want to check if both are not empty, you put all that in another NOT: NOT(AND(ISBLANK(H4), ISBLANK(H5)). Similarly, if you want to check if at least one of the cells is empty, you can use OR: OR(ISBLANK(H4), ISBLANK(H5)). And if you want to check if none of the cells is empty, you can again again put that in an AND.

Categories : Excel

Excel Function Difference Between Two Dates
Change time() with timeserial() Function DIFERENCAHORASMINUTOS(ByVal EntryTime As Date, ByVal ExitTime As Date) Dim hexit, hentry, mexit, mentry, dminutes, dhours As Integer hexit = Int(Hour(ExitTime)) hentry = Int(Hour(EntryTime)) mentry = Int(Minute(EntryTime)) mexit = Int(Minute(ExitTime)) If ExitTime < EntryTime Then If mexit < mentry Then dhours = (hexit + 24) - hentry dminutes = (mexit + 60) - mentry DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0) Else dhours = (hexit + 24) - hentry dminutes = mexit - mentry DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0) End If Else If mexit < mentry Then dhours = hexit - hentry dminutes = (mexit + 60) - mentry DIFERENCAHORASMINUTOS = Ti

Categories : Excel

Turning an excel formula into a VBA function
Here is a simple solution... Building_name = ??? Date = ??? Last_Row = Range("A65536").End(xlUp).Row For i = 1 To Last_Row if cells(i,1).value = Building_Name Then if date >= cells(i,4).value Then if date <= cells(i,5).value Then first instance = cells(i,6).value end if end if end if next you should add a test at the end to avoid the case where there is no first instance in the table

Categories : Excel

Convert Excel 'if' function to SQL query
You can use IIF function as well... Please check the link for description. So for you it would be something like SELECT IIF (a > b, IIF ( B > C, A, B ), C ) AS Result; Sytax is not tested but would work.

Categories : SQL

Excel calling a function of a C++ program
You can call DLL functions from excel. With that, you've got 2 possibilities: Convert your program into a DLL. If that's not possible, create a DLL which communicates with your program (over some IPC mechanism, like sockets for example) For details, search the Web or ask again :-) how would the COM/DLL "know" that excel has invoked it and thus provide some sort of "acknowledgement" and in turn invoke the doSomething() in program.exe Excel doesn't invoke the DLL, it invokes a function in the DLL (by calling it from a VBA macro or a spreadsheet formula). When this function is executing, it must have been called by something (probably Excel, unless you call it from somewhere else) so it kind of "knows" that it's been called. And it can provide feedback to excel through it's ret

Categories : C++

Can I add my own function to the customized status bar in Excel?
Try something like this (Not the best way to do it though but solves your purpose) Explanation: What this code does is checks if the user has selected a valid range (of minimum 2 cells) and then uses the Application.Evaluate to calculate your formula and then display it in the status bar. Also note that I have not done any error handling. I am sure you will take care of it :) Paste this in the relevant sheet code area. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If TypeName(Target) = "Range" Then If Target.Cells.Count > 1 Then Application.StatusBar = "My Function: " & _ Application.Evaluate( _ "=COUNTA(" & _

Categories : Excel

Excel VBA - using left function with find
In order to use the worksheet function Find you need to fully qualify it as follows; Application.WorksheetFunction.Find Using Find on its on and VBA will assume that it is a VBA function. Same goes for match.

Categories : Excel

PHP equivalent for Excel's COMBIN(N,K) function?
Your calculation is failing because you're quickly overflowing the Integer data type, which is probably just 32-bits on your system. You could use the arbitrary precision math functions to get around that problem: http://www.php.net/manual/en/ref.gmp.php If gmp_fact(...) seems to be returning errors or bad results, you're probably passing it a bad value or assuming it's result is a basic numeric type. You'll want to use something like gmp_strval to convert the returned GMP resource into something readable when you're done performing calculations. Example: $a = gmp_fact(1500); $b = gmp_fact(5); $c = gmp_fact(1500-5); $result = gmp_div($a,(gmp_mul($b,$c))); echo gmp_strval($result);

Categories : PHP

Excel QFD (Quality Function Deployment)
This is mainly created using diagonal borders. Increase the Zoom of the worksheet Using the Draw Borders button it is possible to repeatedly draw diagonals - but it takes precision, this is why you need to zoom Merged cells may also be used Once you've got a selection of different borders and merged cells, click in one of these and double-click the Format Painter so that you can repeatedly apply its formatting to other cells When you insert a Shape, and as you drag it, hold the Alt key so that it will fit neatly in the cell If you are inserting an Oval hold the Shift key as you drag it so that it will be a perfect circle, the same for other shapes. (You can hold both Alt and Shift at the same time, but it's probably not necessary) Double or right-click a shape to locate the Format option

Categories : Excel

In Excel VBA creating a wordwrap function
The function: Function WordWrap(ByVal Rng As Range, Optional ByVal MaxLength As Long = 100) As String() Dim rCell As Range Dim arrOutput() As String Dim sTemp As String Dim OutputIndex As Long Dim i As Long ReDim arrOutput(1 To Evaluate("CEILING(SUM(LEN(" & Rng.Address(External:=True) & "))," & MaxLength & ")/" & MaxLength) * 2) For Each rCell In Rng.Cells If Len(Trim(sTemp & " " & rCell.Text)) > MaxLength Then OutputIndex = OutputIndex + 1 arrOutput(OutputIndex) = Trim(Left(sTemp & " " & rCell.Text, InStrRev(Left(sTemp & " " & rCell.Text, MaxLength), " "))) sTemp = Trim(Mid(sTemp & " " & rCell.Text, Len(arrOutput(OutputIndex)) + 2)) For i = 1 To Len

Categories : Arrays

Excel VBA StrCmp sub or function not defined
The functions name is StrComp and to call it you would say : StrComp( StringArg1, StringArg2, compareMethod) = 0 for a match. It seems like in your case you would use if StrComp(Cstr(Worksheets(1).Range("I" & x).Value), "Critical", vbTextCompare) = 0 then ' you now have found a match ' do something end if More on the StrComp(), also here and also see this here on SO

Categories : Excel

Trying to use the if function in Excel with a date and a number
step 1 - create the IF statement so the recipient cell contains X or O depending of the condition being met or not step 2 - create a conditional formatting depending of the content of the cell (X or O) let me know if I need to elaborate with the actual formula. Hint: check the AND() Excel function Thank you

Categories : Excel

Excel macro to insert function
Consider: Sub dural() Dim s As String s = Range(ActiveCell, ActiveCell.End(xlDown)).Address ActiveCell.End(xlDown).Offset(1, 0).Formula = "=SUM(" & s & ")" End Sub

Categories : Excel

VBA Excel Function Error Runtimeerror 13
You are getting an error on the line calweeks = fillcalweeks(weeks) because you are assigning a String array (the result of the function fillcalweeks to a Variant. You'll notice you declared every variable except calweeks. Since VBA doesn't have an explicit declaration for this variable it assigns it to a Variant. To fix the problem, start by putting Option Explicit at the top of every module. When you compile the project, it'll alert you to errors such as these (Debug -> Compile VBA Project). Then all you need to do is declare calweeks as a String(). There is a second problem you have and that is that you are trying to store a Date data type in your String() array within the fillcalweeks. You either need to convert the datestart, dateend and calweek variables to Strings (you can us

Categories : Excel

Excel rounddown function equivalent in php
Use Round - float round ( float $val [, int $precision = 0 [, int $mode = PHP_ROUND_HALF_UP ]] ). You need to pass PHP_ROUND_HALF_DOWN as 3rd parameter(Mode). round($value, 4, PHP_ROUND_HALF_DOWN); If mode parameter is not available in your version of PHP(as it didn't work for me) then use this: floor($value * 10000) / 10000;

Categories : PHP

Excel Drop Down + search function
You can use: Cascading dropdown (such as xls cascading lookup based on pivot table style datasource? and http://www.contextures.com/xlDataVal02.html) Use a formula in the Named Range for your List Validation that will simulate an autocomplete Create a named range Test with this kind of formula : =OFFSET($A$2,MATCH($C$1&"*",$A:$A,0)-2,0,COUNT($A:$A)) Where: your list of data is in column A (values should be sorted alphabetically) the current cell where you are applying the validation on is C1 Then you can: start typing the text you want in cell A1, for instance Wash then click on the drop-down list the dropdown list will begin at Whashington and so on I've just translated it from one of my old workbook so tell me if anything doesn't work. [EDIT] Just a quick test seem to make

Categories : Excel

Excel VBA function not working for Class
SumUp(result = sumValue, number = numValue) This is invalid on many levels. If you meant to call SumUp with named parameters, you should have written SumUp result:= sumValue, number:= numValue You don't have to use named parameters though: SumUp sumValue, numValue

Categories : Vba

Excel Function within SSRS 2012
Short answer is no. As you've probably seen in your research, there was limited support for this in SSRS 2005 but not in subsequent versions. See Breaking Changes in SSRS 2008. In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel, RDL expressions are not translated to Excel formulas. Needless to say, this is a popular request, see this Connect item: SSRS 2008 export formulas to Excel. While refreshing my memory about all of this, I did see a few notes that suggested if you use expressions which directly reference the ReportItems collection like: =ReportItems!val1.Value + ReportItems!val2.Value Then this would work, but I ran a simple test now

Categories : Reporting Services

Replicate Excel's mode function using SQL?
Below statement return mode value if it is single, mode value of the first (alphabetically) Vendor if set is multimodal, and return NULL if all values unique (no mode exist). ;with m as ( select *, aCnt = count(1) over (partition by amount), tCnt = count(1) over () from TableName ) select top (1) case when acnt = 1 and tCnt > 1 then NULL else amount end as mode from m order by acnt desc, vendor; SQLFiddle sample To find mode within (inputfile, code, period) tuple you may try: ;with r1 as ( select inputfile, code, period, vendor, amount, acnt = count(1) over (partition by inputfile, code, period, amount), tcnt = count(1) over (partition by inputfile, code, period) from TableName ), r2 as ( select inputfile,

Categories : Sql Server

"Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
According to MSDN Application.Volatile : Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell. This (and other) mean that: instruction works only with functions, not Module it will work with all User Defined functions also as a result of pressing F9 it's required to place the instruction at the beginning of UDF. See example of UDF with Application.Volatile: Function ReturnCurrentTimer() Application.Volatile ReturnCurrentTimer = Timer End Function

Categories : Excel

What exactly is the function of Application.CutCopyMode property in Excel
By referring this(http://www.excelforum.com/excel-programming-vba-macros/867665-application-cutcopymode-false.html) link the answer is as below: Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard. With optimised VBA code you can usually do the copy/cut and paste operations in one statement, so the clipboard isn't used and Application.CutCopyMode = False isn't needed and you

Categories : Excel

Excel - VBA : Make the "replace" function more specific
Do this: sMain.Range("J3").Replace " VA ", "V. A." then handle the cases where the original string starts or ends with VA also, handle all cases of separators which could be (for example) tab, space or comma. To do that: const nSep As Integer = 3 Dim sep(nSep) As String sep(1) = " " sep(2) = vbTab sep(3) = "," for i=1 to nSep for j=1 to nSep sMain.Range("J3").Replace sep(i) & "VA" & sep(j), "V. A." next next

Categories : Excel

Need guidance with a VBA function to paste values in Excel
You can just ''transfer'' the value(displayed) over like this Function PASTEVALUE(rng As Range) PASTEVALUE = rng.Text End Function or use the Evaluate() function to evaluate the formula in that range Function PASTEVALUE(rng As Range) PASTEVALUE = [rng] End Function

Categories : Function

Substitute Function in Excel VBA for cell range
Try this. Note that it uses the VBA Replace function, so you need to rename your 'Replace` subroutine. Sub ReplaceText() For Each c In Sheets("Sheet1").Range("A1:A629").Cells c = Replace(c.Value, ",", ";") Next c End Sub Note: This will only work if you have values in the cells, no Formulas. Because Excel has a formula length limit of 1024 characters. But given you have this specific error, your cells must not be formulas.

Categories : Excel

excel database function in combination with vba, what if there are no records?
This will check the number of visible cells after the AutoFilter is applied: Selection.AutoFilter Field:=2, Criteria1:="yes" If ActiveSheet.AutoFilter.Range.Rows.Offset(1, 0).SpecialCells(xlCellTypeVisible).Count - ActiveSheet.AutoFilter.Range.Columns.Count > 0 Then Range("B3").Select Range(Range("b3"), Range("b2").End(xlDown)).Select Selection.Copy Range("B12").Select ActiveSheet.Paste End If The - ActiveSheet.AutoFilter.Range.Columns.Count part is to subtract the header cells from the count. FWIW, when I walked through your original code, I got the 1004 because the Copy area was from B7 to the bottom of the sheet (the effect of xlDown in an empty selection).

Categories : Excel

I need helping writing an excel vba Splat function
Try something like this, using the Worksheet_Change event: Sub Worksheet_Change(ByVal Target As Range) 'Assume the workbook has a named range 'lookuptable' at G3:H6 'Assume the workbook has a named range 'lookupColumn' at D3:D6 If Target.Cells.Count <> 1 Then Exit Sub If Intersect(Target, Range("lookupColumn")) Is Nothing Then Exit Sub If Not Left(Target, 1) = "/" Then Exit Sub UpdateLookupTable Target End Sub Sub UpdateLookupTable(cl As Range) Dim r As Long Dim c As Long Dim splatVal As String splatVal = Replace(cl.Value, "/", 0, , 1) r = Application.Match(cl.Offset(0, -1), Range("lookuptable").Columns(1), False) Range("lookuptable").Cells(r, 2).Value = splatVal cl.Value = splatVal End Sub I was not sure whether you intend

Categories : Excel

Excel VBA macro for a complicated Copy Function
Change the workbook and sheet values to the applicable workbook and sheets Sub usermove() Dim c, Rng1 As Range Dim i As Integer i = 1 'ask for input userselect: ThisWorkbook.Sheets("Sheet1").Activate Set Rng1 = Application.InputBox("Please Select Cells", Type:=8) 'loop For Each c In Rng1.Cells ThisWorkbook.Sheets("Sheet2").Activate Cells(i, 1).Value = c.Value i = i + 1 Next 'Prompt for more input continue = MsgBox("Are there more cells to select?", vbYesNo) If continue = vbYes Then GoTo userselect 'then save ChDir "path" ActiveWorkbook.SaveAs Filename:="pathBook1.txt", FileFormat _ :=xlText, CreateBackup:=False End Sub

Categories : Excel

Replicating Excel's CHIDIST function in Oracle 11g
Right tail probability... nice... In oracle you can embedded java code into stored procedures and functions. The best way is to use proper java class and call it from oracle's function. It is not that hard: http://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm http://jwork.org/scavis/api/doc.php/umontreal/iro/lecuyer/probdist/ChiDist.html That is all I can do for you: DECLARE l_value_to_evaluate NUMBER (10, 3) := 18.307; /* X; Value at which you want to evaluate the distribution */ l_degrees_freedom NUMBER := 10; /* Degrees of freedom */ l_number NUMBER; FUNCTION is_number(str_in IN VARCHAR2) RETURN NUMBER IS n NUMBER; BEGIN n := TO_NUMBER(str_in); RETURN 1; EXCEPTION WHEN VALUE_ERROR THEN

Categories : Oracle



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.