spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » EXCEL » Page 1
Closing Excel window with ZK and Jasper Report
Currently, there is no browser that can display an Excel file directly because there is no corresponding plugin. If displaying Excel is not your priority. I have 2 suggestions: Let users download Excel without opening new window In your controller, just create a excel file and let users download Refer to http://www.zkoss.org/zkdemo/file_handling/file_download Close new window with JS Close

Categories : Excel

Concatenate if cell is not blank
Are you trying to do this? =CONCATENATE( "site_refer=", B8, "+", D8, "+", E8, "+", F8, IF(G8 = "" , "" , "+"&G8 ), "&keyword_refer=", H8 )

Categories : Excel

Assigning numerical value to a column in excel to total a list in excel
If Upgrade cost is in B1, and Replace cost is in C1, then place this in D2 and copy down: =(B2*$B$1)+(C2*$C$1) If you enter something like $500 in the 'Upgrade' cell, and the format of the cell is set to "General" then Excel will assume that this is a currency format unless you tell it otherwise. Alternatively you could just use plain numbers and format the cells afterwards.

Categories : Excel

Show certain rows but keep others hidden
Rough outline - UNTESTED CODE... something like this should unhide everything, check each checkbox for status, add checked boxes to range, and hide that entire range at end. 'CheckBox1 is Row 1 in group 'CheckBox2 is Row 2 in group 'CheckBox3 is Row 3 in group 'CheckBox4 is Row 4 in group 'CheckBox5 is Row 5 in group 'CheckBox6 is Group A 'CheckBox7 is Group B 'CheckBox8 is Group C 'CheckBox9 is

Categories : Excel

can 64 bit MS office install be rolled back to 32 on Win 8.1 64 bit machine
You need the 32-bit version of office to install it. If your installation package has the 32-bit binaries you can simply uninstall the 64-bit version and install the 32-bit version in its place. The way to get the 32-bit code to work is to add the PtrSafe attribute to your calls to external, 32-bit libraries. For example: Public Declare PtrSafe Function Lib "externalLib32" Alias "libFunc" (ByVa

Categories : Excel

How can I multiply a range specified by a variable with its offset in VBA?
This is not correct: oCell = Range("rCell").Offset(1, 0).Select rCell is not name of range but range itselve. Try: oCell = rCell.Offset(1, 0).Select Also you dont need to select the range but get the range value. So this would be more correct: oCell = rCell.Offset(1, 0).Value

Categories : Excel

Generate full folder structure (topfolders, subfolders, subsubfolder, etc) via excel
The data provided does not provide clear definition as to what is needed. (ie. What sub-folders belong to what parent folders) The best advice I can give you at this time is to research the MkDir() function. http://msdn.microsoft.com/en-us/library/k1d22wfh%28v=vs.90%29.aspx Keep in mind that this function can not build an entire directory structure at once, it can only add a single directory to

Categories : Excel

Want to check text box value for duplicates in column before I add to spreadsheet
Using subroutines, you can perform this more easily than in one big chunk. You would need to set your values for certain variables like "ws" upon UserForm activation. Your Save Button click event would be just a few lines then, executing each subroutine. First checking for Duplicates, then Checking for Empties, then Copying the Data, Saving the Workbook, and Clearing the Form for another entry.

Categories : Excel

Display the 'File -> Print' menu via VBA
Is this what you are trying? Sendkeys are unreliable so you have to be very careful while using them. Ensure that you call it from Developer Tab | Macros and not directly from the VBA Editor. Else you will have to use APIs to bring the Excel window to the front and then use sendkeys. Sub Sample() SendKeys "%fp" End Sub Here is an example to call it from VBE Private Declare Function SetF

Categories : Excel

iserror function for mode - vba excel
try this On Error Resume Next Stage = Application.WorksheetFunction.Mode(Sheet3.Range("I" & finalrow, "Y" & finalrow)) If Err <> 0 Then Stage = 0 ' or whatever you want the stage to be if Mode has error Err.Clear End If On Error GoTo 0

Categories : Excel

VBA separate range of dates in cells with duration
Following code may help to solve your problem. 'Function print_date ' 1st argument: date of period begin ' 2nd argument: date of period end ' 3rd argument: duration in days ' ' CAUTION: This code does not check consistency between dates and duration. ' If you set duration 1.5 between Nov-3 to Nov-5, this code raise no error ' and you may see duration -0.5 on Nov-5. Function print_date(begin

Categories : Excel

Excel 2013 advanced date range lookup using sumproduct
The SUMPRODUCT is a kind of swiss army knife Excel function. But here it is wrong because, as you already have seen, it really calculates a SUM at the end. Mostly it works because it first multiplies the 0 or 1 of the conditions with the row numbers so only the row numbers which fulfills the conditions comes into the sum. But if two or more row numbers fulfil the conditions then they were added to

Categories : Excel

Excel: give names of cells if condition is met
You can use this VBA function. Call it by passing the line (range) with headers (ingredients) as first parameter and the line (range) with amounts (of whatever) as second. You can use this function like a native Excel function after putting it in a VBA module. (It doesn't checks for correct input here, so both ranges (headers and amounts) have to contain the equal count of cells) Function Ingredi

Categories : Excel

Excel 100% Pie chart
I am not sure what you require. Something like this maybe?: This involved: summing all the ColumnG values other than G1 in G1, =SUM(G2:G1048576) plotting columnG as Style 6 pie chart. =COUNT(G:G)-1 in H1 (returned 6) select the 'other' part of the pie (right-hand side) and for Series Options, Values in second plot entering the value for the previous step (ie 6). some formatting adjustmen

Categories : Excel

Summation in excel?
First: Such formulas have to be array formulas. But your formula seems not to be the exact Excel formula to "sum from i=1 to n of (a^n * b^(n-i))" even as array formula because in my opinion $A$10:A10 is the equivalent to i and not n. So {=SUM($B$3^$A10*$B$5^($A10-$A$10:$A10))} is the right Excel formula in my opinion. To create a array formula put the formula into the cell without the curly

Categories : Excel

User-Defined Function to Get Hyperlink Address from Cell
Your code works fine, except that you just have to force the UDFs to recalculate, otherwise its result doesn't get updated. If you want it to recalculate automatically, you can use Application.Volatile (and you can drop the unnecessary temp): Function GetAddress(myCell As Range) As String Application.Volatile GetAddress = myCell.Hyperlinks(1).Address End Function Now you won't have to m

Categories : Excel

Excel Pivot Sub total columns
The easiest way would be to create a new column in your source data. Perhaps, Business Type and label it accordingly. Then just rearrange the pivot table as you see fit.

Categories : Excel

using SMALL function to retrieve lowest value in range, trying to combine with OFFSET to get value to the left
Is OFFSET the correct way to do this? - No OFFSET expects as its first parameter a range reference, not a value. If your prices are unique, use this (if they are not, I'm not sure what result you would expect) =INDEX(A2:F2,MATCH(H2,A2:F2,0)-1) That said, your SMALL formula looks suspect. It looks like you want to return "" if all the prices are 0. But your formula will return the smallest i

Categories : Excel

Connecting two columns in MS Excel
You provide very little information about your spreadsheet. I think you are after dependent data validation, where the first cell offers a choice of, say "Continent" and you can select "America", "Africa", "Asia", "Europe", etc. and after you have made a selection, the next cell will present a choice of countries in the selected continent. If that is what you want to do, take a look at dependent

Categories : Excel

Drag all measure values from SSAS cube pivot table
It depends what tool you are using to view the cube. In SQL Server Management Studio, you are able to drag the entire list of measures across in one go (but not individual measure groups) In Excel, however you are limited to single measures at a time. You can "Defer Layout Update" so that a refresh does not take place between each measure selection, which will help your end users speed up mul

Categories : Excel

Using Left without Copy & Paste
If I understand you correctly, your wanting to replace the contents of a cell in column C with the first 12 digits of whatever is in that same cell. Starting with data that looks like this: Run This: Sub keep12() Dim wks As Worksheet Set wks = Worksheets("Sheet1") Dim rng As Range Set rng = wks.Range("C3:C" & wks.Range("C" & wks.Cells.Rows.Count).End(xlUp).Row)

Categories : Excel

Excel VB code to handle an array that has commas in the filenames
This looks impossible for me, because someone could name his file document.txt, something.doc (yes this is one file looking like two!). Even if this makes absolutely no sense, this is considered as a valid file name and you will be unable to detect these. You can't build a secure function to filter that. The only approach I could imagine is to loop your array and check for every item if the f

Categories : Excel

VBA Click Link in IE Ajax PopUp
I'm not sure if you can get any data from the pop-up, but if you can, try to use getElementsByTagName method and the outerText property. For Each Link In ieDoc.getElementsByTagName("a") Do If Link.outerText = "The link text" Then Link.Click Exit For End If Next You can find more information here Hope it help! Best regards, Luiz Fernando

Categories : Excel

Count multiple colors in multiple excel files vba
This will log your Red and Yellow cells in a Sheet called "Log Sheet" in the workbook from which you run this code. Add/name this sheet and any formatting you require yourself. Getting the cell colour of conditionally formatted cells is tricky but you may find help in this article. I have included a column in the Log Sheet to identify IF there are conditionally formatted cells in the worksheet b

Categories : Excel

run-time error - 2147217900 80040e14
It is recommended to run large queries via recordset and not like sheet query table. To understand the IN operator see this. This is example how you can build and run your query: Sub BuildAndRun_Sane_Query() ' Create IN_string: Dim SN, S_In As String, s_query As String For Each SN In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) ' Define your input range S_In = S_In & IIf(S_In

Categories : Excel

I need formula in column name "FEBRUARY"
Try this SELECT T2.ID_ENTRY, T1.TAG, T1.TAG2, T2.Name, T2.LastName, T2.Age, T2.Tel FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ID = T2.ID GROUP BY T2.ID_ENTRY, T1.TAG, T1.TAG2, T2.Name, T2.LastName, T2.Age, T2.Tel HAVING Count(T2.ID_ENTRY) > 1

Categories : Excel

VBA - Excel VBA Set Horizontal & Vertical alignment
You can get a yes/no msgbox but usually an input box is required for input values. Such as. Dim chemin1 As Object Set chemin1 = ThisWorkbook.Worksheets("Sheet1").Range("A1:J10") With chemin1 Dim length As Integer Dim ship As String Dim message_box As String message_box = InputBox("V or H") If message_box = "V" Then ActiveCell.Range("A1:A5").Interior.Color = RGB(12

Categories : Excel

Change Value in Excel According to a Reference Table based Upon a Selection
Use the VLOOKUP function. The syntax is: =VLOOKUP(lookup_value,look_up_table_range,column_index,FALSE) Lookup value should refer to apple or banana in the sheet/table you want to update, the look up table range refers to the table of the fruits and their id (use $ in the reference as below to ensure this does not move when you click and drag). In this the function will look up the value in the l

Categories : Excel

Can't execute macro for more than one items from listbox
I think your problem is here: Range(rRng, rRng2).Select You probably mean to do this: Application.Union(rRng, rRng2).Select in my test rRng was A1,A5,A8 rRng2 was C1,C5,C8 Range(rRng, rRng2).Select 'results to select range("A1:C1") Application.Union(rRng, rRng2).Select 'results to select cells A1,A5,A8,C1,C5,C8 Offtopic: let me recommend you to use With blocks and dont use select

Categories : Excel

Propagate values in excel cell
I think you will find the LEFT, RIGHT, and MID functions useful. If you were to put all the values that you need to split, like 01110011 (the binary string you used as an example), in column A, you could split it in columns B, C, D and E with the following formulas: Column B: =LEFT($A1,1) Column C: =MID($A1,2,3) Column D: =MID($A1,5,2) Column E: =RIGHT($A1,2) The LEFT function takes a

Categories : Excel

concatenating two part-words together
Assuming your name, address and phone number are in different columns I'd use a COUNTIF statement in Column D to identify those at the same address (assuming they all live together!) You'll need to adjust the range, and insert a blank column into Column A. =IF(COUNTIF($C$2:$C$9,C2)>1,"Mr & "& C2,"") This will concatenate all the couples, you can then use the below formula in a differ

Categories : Excel

Using AND and OR clauses together in MS Excel IF function
I'm making some assumptions here about what you want to happen, specifically what should happen based on which side of the AND statement causes it to evaluate to false, but I think I understand. I would use the following formula: =IF(D18<=450,IF(OR(F18="O",F18="V"),1330,1450),IF(OR(F18="O",F18="V"),1550,1660)) This is embedding an additional conditional in your THEN and ELSE clauses.

Categories : Excel

Object-defined VBA error 1004
With the information you have provided, I think the error lies either on ConstraintsSheet or on ProfessorRow. If you say: "ConstraintsSheet and ProfessorRow are definitely defined, as are the UserForm and OptionButton names", it means that what you're trying to do is this: Sheets(UserForm1.Name).Cells(UserForm1.OptionButton1.Name, 3) = "Y" So, a possible problem might be that there's not a she

Categories : Excel

Excel Aggregate - adress of the result?
In D1 enter: =ABS(A1-B1) and copy down then use: =INDEX(C:C,MATCH(MAX(D:D),D:D,0)) For example:

Categories : Excel

How can I determine the type of a shape on a worksheet containing a variety of Shapes?
If you run: Sub IdentifyShapes() Dim s As Shape For Each s In ActiveSheet.Shapes MsgBox s.Type & vbCrLf & s.Name Next s End Sub You will find that drop-downs for Data Validation are type 8 and have names like Drop Down 1 Experimentation will yield the types for all the Shapes on your Sheet.

Categories : Excel

sums based on criteria in excel
I hope I clearly understand your problem and I would like to propose you a solution without writing any VBA code. Please note that it will work only if data set column in your second table contains unique values (but if not, it should not be difficult to find a workaround). You may add a column for every drive you have in your First table after Penalty column, as I did in the picture here below:

Categories : Excel

How to write and run excel function in data validation ms-excel
You can simply create these three formulas in Column B, say, MIN in B1, MAX in B2, SUM in B3. Then pull your data validation drop down list from these cells which already contain that formula. However you need to format your B1, B2, B3 cells so that in data validation list you see for example MIN, MAX or SUM. I have done this for you in this dropbox link like this: here is the preview You

Categories : Excel

Speed up Excel PowerShell modification to file
Using the builtin Replace() method on the entire column will be a lot faster than changing each cell individually: $colors = @{ 'Blue' = "'001" 'Red' = "'002" 'White' = "'003" } $wbName = 'C:Usersw0517DesktopColorCodes.xlsx' $wsName = 'Sheet1' $xl = New-Object -COM 'Excel.Application' $wb = $xl.Workbooks.Open($wbName) $ws = $wb.Worksheets.Item($wsName) $colors.Keys | % { $ws.Cells.I

Categories : Excel

Error while copying more than 8202 characters from one cell to another
Change your code to Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value and it will work. Not really sure why though, as .Value is the default property of a range.

Categories : Excel


Recently Add
Excel with Qt: find number last filled row
Closing Excel window with ZK and Jasper Report
Concatenate if cell is not blank
Assigning numerical value to a column in excel to total a list in excel
Show certain rows but keep others hidden
can 64 bit MS office install be rolled back to 32 on Win 8.1 64 bit machine
How can I multiply a range specified by a variable with its offset in VBA?
Generate full folder structure (topfolders, subfolders, subsubfolder, etc) via excel
Want to check text box value for duplicates in column before I add to spreadsheet
Display the 'File -> Print' menu via VBA
iserror function for mode - vba excel
VBA separate range of dates in cells with duration
Excel 2013 advanced date range lookup using sumproduct
Excel: give names of cells if condition is met
Excel 100% Pie chart
Summation in excel?
User-Defined Function to Get Hyperlink Address from Cell
Excel Pivot Sub total columns
using SMALL function to retrieve lowest value in range, trying to combine with OFFSET to get value to the left
Connecting two columns in MS Excel
Drag all measure values from SSAS cube pivot table
Using Left without Copy & Paste
Excel VB code to handle an array that has commas in the filenames
VBA Click Link in IE Ajax PopUp
Count multiple colors in multiple excel files vba
run-time error - 2147217900 80040e14
I need formula in column name "FEBRUARY"
VBA - Excel VBA Set Horizontal & Vertical alignment
Change Value in Excel According to a Reference Table based Upon a Selection
Can't execute macro for more than one items from listbox
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.