FAQ Database Discussion Community


How to insert excel formula to cell in Report Builder 3.0

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

timestamp SQL to Excel

php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

Excel: match two columns with two other columns

excel
in excel, I have four columns. Columns A & B correspond with each other and columns C & D correspond with each other. What i'd like to do is create a formula that takes a value from column A, searches through column C, looking for a match. If it finds...

VBA search each cell iteratively for a string

excel
I'm working on equipment databases for my job, and I'm trying to search for a variable string in each cell of a column, and once that string is found, I need to assign another variable to the value of the cell in the column next to the column I'm searching....

Sumifs and Criteria - how to set “all” criteria?

excel
I have a table that is like this - starting in cell B1: Type Amount Bat 123 Bat 321 Bat 123 Bat 354 Car 154 Car 156 Car 15688 Car 154 I have a SUMIF that will look at the Type, and return the Sum. In "A1" I have a...

if condition inside Insert query in excel?

mysql,excel
i'm trying to turn my records in excel into an insert query. i've fields empty in certain situations. In such case, NULL should be inserted. I've written the formula as below but it is not working/showing error. Think i've missed something. ="INSERT INTO table_1 VALUES(" &A2 &",'" & B2 &...

Creating dynamic dropdowns in Excel where values may appear in more than one column

excel,excel-formula
Normally, where the values in the column of a lookup array are unique there is only a need to match the value in the last dynamic data validated list with the value in the relevant column of the lookup array to provide the range of values for the next dynamic...

Identifying cell in Openpyxl

python,excel,openpyxl
I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

Referencing a new inserted column Excel VBA

excel,vba,excel-vba
I am trying to reference a cell in the below formulaes. 'AUA Summary'!$D$9 . Each time the macro runs a new column D is inserted. The Problem: When the column is inserted my reference moves to ** 'AUA Summary'!$E$9. How do I get to reference 'AUA Summary'!$D$9 even if a...

Counting across rows for ###, then countif down columns for ids matching ###

excel,excel-formula,countif
I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities. The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes") However, I now need to group organizations by amount of activities (ex:...

How to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use Power View to view, filter, and highlight my data. However I haven't figured out a way to link my table rows to the data source (i.e. tables in other tabs of the Excel spreadsheet). so that if I double-click on a row, Excel will...

Unable to extend VLOOKUP formula

excel,excel-2010
A quick question on VLOOKUP. I have two sheets ("Sheet2" acting as source or list of elements and "Sheet1" is where the VLOOKUP formula will be used) I have created a name so that I can reuse the vlookup formula for A2 (Sheet1) also. The issue is when I drag...

Excel VBA Loop Delete row does not start with something

excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

Lookup and cell referencing

excel
I am trying to use the lookup formula. This is the formula I use: =LOOKUP(D4,Roster!C3:C184,Roster!D3:D184) Naturally I would have to copy this down the column. The problem I am having is that when I copy it down the column, the references also change. =LOOKUP(D5,Roster!C4:C185,Roster!D4:D185)<br /> =LOOKUP(D6,Roster!C5:C186,Roster!D5:D186)<br /> etc What I...

Comparing cell contents against string in Excel

string,excel,if-statement,comparison
Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Transpose in excel doesn't work, it just copies one cell

excel,transpose
I've been researching for quite a while and I cannot find the solution to this problem. I need to switch the values of some rows and columns in excel and I saw that I could use transpose, put the array I need to swap and press CTRL+SHIFT+ENTER. I am doing...

VBA - .find printing wrong value

excel,vba,excel-vba
I have two sections of code that basically do the same thing but with two different columns. The code finds the header "CUTTING TOOL" and "HOLDER" (looping through multiple files) and prints the information from those columns into one worksheet, masterfile. I was using a less efficient method of setting...

Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

Errors 91 and 424 when iterating over ranges in Excel VBA

excel,vba,excel-vba,range
I am an absolute VBA beginner. I have been trying to create a function that separates a large range into smaller ranges. However, when I try and iterate over the large range, I get errors 91 and 424 interchangeably. Here is the relevant bit of code: Dim cell As Range...

Retrieve Number from a website into Excel

excel,excel-vba
From this website http://bit.ly/1Ib8IhP I am trying to get this number into an Excel cell. Avg. asking price in Bayswater Road: £1,828,502 Is there any way using VBA or another tool? Couldn't make it work with a web query....

Best way to return data from multiple columns into one row?

excel,vba,excel-vba
I have a sheet with just order numbers and another with order numbers and all of the data associated with those order numbers. I want to match the order numbers and transfer all of the available data into the other sheet. I've been trying to use loops and VLOOKUP but...

Excel VBA (via JavaScript) - Moving a Sheet to a new location

javascript,excel,vba,excel-vba
With the following code, I am attempting to move a Sheet in my Excel workbook from one location to another. However, instead of making the move - Excel creates a new Workbook. How do I move a Sheet from one location to another within the same Workbook? /////////////////////////////////////////////////////////////////////////// // //...

display inside a column the number from a string

excel
I have one column (B) ( over 3k cells ): TestUrl __________________________________ http://www.testing.eu/test123.html http://www.testing.eu/test154.html http://www.testing.eu/test983.html .. and so on ... I want in column C, to get only the numbers: Numbers __________ 123 154 983 ..and so on.. How can I achieve this?...

Microsoft Power BI Designer data model to Excel or PowerPivot

excel,microsoft,powerpivot,powerbi
Is there a way to get a Microsoft Power BI Designer data model into Excel to work with in Powerpivot? Thanks in advance....

Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

excel,vba,excel-vba,sorting
I am trying to sort these three columns (Sort By Col-2) in excel using VBA. Top-left (Row number and Column number e.g. 1,1) and lowest-right cell (Row number and Column number e.g. 9,3) are known. Every cell contains the values of String type. Input: Col-1 Col-2 Col-3 P1 I1 XYZ...

Export data to Excel sheets ? Not one sheet?

c#,excel,export,foxpro,sheet
How can I export data from Visual FoxPro into Excel file I know. I something like this: USE tableName EXPORT TO (fileName) TYPE XL5 AS CPDBF() I get Excel file with one sheet? Does anybody know how can export second table to the same excel file but in different sheet....

Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

excel search engine using vba and filters?

excel,vba
I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

Changing the active cell

excel,vba,excel-vba,excel-2007
I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

12 Characters Including leading and following zeros

excel
I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

Label linked to a text box value

excel,vba,textbox,label
Good morning, I am editing an User Form on VBA Excel and I would like to show an alert if the user insert a certain value in a text box. I wrote this code: If txtbox.Value < 0 Then lbl_Alert.Visible= True Else lbl_alert.Visible=False End IF The code works properly but...

How Do I Transform This CSV / Tabular Data Into A Different Shape?

excel,csv
I have a sparse n-column spreadsheet where the first two columns describe a person and the rest of the (n-2) columns track RSVP and attendance data for various events (each of which take up one column). It looks like this: PersonID, Name, Event29108294, Event01838401, Event10128384 12345, John Smith, Registered -...

Hiding #DIV/0! Errors Using IF and COUNTIF in Excel 2010

excel,vba,if-statement
I'm working on a tracking sheet for quality reviews of work completed. I have a list of criteria to be met for which the entry can be either Y or N, or X for not applicable. Each month a number of these reviews will be done on each person. In...

How do I get a cell's position within a range?

excel,vba,excel-vba
How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range. I considered using the position of...

Conditional Maximum in Excel using Array Formulae - how to ignore Blank Rows

excel,array-formulas
There are quite a few questions on Stack Overflow about doing a conditional MIN and MAX in Excel e.g. Excel: Find min/max values in a column among those matched from another column However, I don't think the following question is covered. Normally the MIN and MAX functions will ignore blank...

Concatenate N number of columns between 2 specific column name in VBA Excel

excel,vba,excel-vba
I am trying to concatenate the selected range between 2 specific columns. My first column name is "Product-name" (First column is fixed) and second specific column is not fixed. It can be 3rd, 4th, 5th or N. The name of that column is "Price". I want to concatenate all columns...

How to reshape data frame in R or Excel?

r,excel
Here's the code to get a sample data set: set.seed(0) practice <- matrix(sample(1:100, 20), ncol = 2) data <- as.data.frame(practice) data <- cbind( lob = sprintf("objective%d", rep(1:2,each=5)), data) data <- cbind( student = sprintf("student%d", rep(1:5,2)), data) names(data) <- c("student", "learning objective","attempt", "score") data[-8,] The data looks like this: student learning...

Excel - select a cell based on adjacent cell value

excel
I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

Doing a same action to all the subfolders in a folder

excel,excel-vba
Given below code converts all xlsx files inside "C:\Files\Bangalore" to csv files. Sub xlsxTOcsv() Dim sPathInp As String Dim sPathOut As String Dim sFile As String sPathInp = "C:\Files\Bangalore\" sPathOut = "C:\Files\Bangalore" Application.DisplayAlerts = False sFile = Dir(sPathInp & "*.xlsx") Do While Len(sFile) With Workbooks.Open(fileName:=sPathInp & sFile) .SaveAs fileName:=sPathOut &...

Need to export tables inside a
as excel, keeping filled-in input and option data

javascript,html,excel,export,element
I have a page that takes some selections in javascript and makes a recommendation based on the inputs. Some of my customers want to be able to save this data in excel format, but I'm running into issues retrofitting that. Here is the code that I found to export the...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

K-Means Clustering a list of US addresses based on drive time

excel,matlab,cluster-analysis,k-means,geo
I have 8 traveling consultants that need to visit 155 groups across the continental united states. Is there a way to find the optimal 8 regions based of drive time using k-means clustering? I see there are some methods implemented already for other data sets, but they are not based...

Dates not recognized as Dates, despite format

excel,datetime
I have imported a column with many dates, but Excel will NOT read them as dates for some reason. I have looked around and tried doing "Text to Columns" and using "DMY" format. I have also tried simply changing the format of the cells to Date (also Custom 'dd/mm/yyyy'), but...

Simple Enquiry with Complex Answer - How do I Select RowA6-Row(last non-blank) for a simple formula

excel,excel-vba,cell,calculated-columns,calculated-field
I have many columns all labeled with many many values underneath, which can be words or numbers Here is the current equation =INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0))) I have this on the in cell 5 of each column. The number of values in each column may increase or decrease. If i reference the entire...

How do I store a SQL statement into a variable

sql,excel,excel-vba
I am currently facing a problem here. I have a column called "DESC1" in a table called "Master". I'm trying to retrieve the value based on something along the lines of this... "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " And I'm trying to display on the...

How do I do to count rows in a sheets with filters? With a suppress lines

excel,vba,filter
I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function
I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

Disaggregate one row of data to multiple rows

r,excel,statistics,dataset,google-adwords
Goodafternoon! I am having some trouble with my dataset. I am using a Google AdWords export for data analysis and I want to fit a logit regression model to the data to determine whether an experiment I have conducted impacts the conversion. The problem is that the data is aggregated...

Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting
I have 2 worksheets with the same headers in different orders. Headers are I.D, Name, Department, Sales, Start date, End Date and a few others. What I am aiming to do is search through the workbooks in which the headers may be in different orders, find the column which has...

Converting column from military time to standard time

r,excel
I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

excel
I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

How to write formula for cell in Excel?

excel
I'm working on excel where I've few columns. I would like to make SQL insert operation from the records in sheet. There are chances of cell to be empty and this is where I am unable to continue. I need to check: if(cell is empty) insert null else insert value...

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet
I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Excel vlookup expansion

excel
I currently have an excel worksheet with three columns id annotation person_id 1 yes 1 1 no 2 1 yes 3 I'm trying to reformat this on another worksheet into a table that looks like: id 1 2 3 1 yes no yes I'm using this vlookup: =VLOOKUP(A2,sheet2!$F$1:$G$10,2,FALSE) where a2...

Specifying a range to be applied to all subs in a module?

excel,vba
I have been working on creating a module that has multiple subs and functions that all are applied to the same sheet. In my efforts and research to clean up my code I found that instead of declaring the "Dim" for each sub, I can declare it at the very...

Excel DNA how to get events for each minor change in spreadsheet?

c#,excel,vsto,excel-dna
Developing excel spreadsheet real-time sharing application I got stuck on how to get events for border change, colour change,multi cell editing etc...(used VSTO but problem still sustain...) I tried Excel DNA for to make RTD server to get real time data to excel but how to send changes in sheet...

How to convert excel data to json at frontend side

excel,user-interface
I want to convert some excel data to JSON. Plan is to get my excel file from D drive, read data and make some UI for this. Can any one please help me out? Data is like this :- country year 1 2 3 4 Netherlands 1970 3603 4330 5080...

How to output a single file for each row of an excel file read in?

vb.net,excel,visual-studio-2010
How can I output a file for each row of excel data? Right now it outputs the correct number of files but has the rows of data incremented, so file 1 is correct, file 2 has row 1 and row 2, etc. Dim smNum As Integer = 0 If rowct...

Type Mismatch Error in DLOOKUP Function

excel,vba
I am getting Type mismatch erro in below code where unit,primepro and subpro datatypes are given as 'TEXT' Private Sub Status_AfterUpdate() If (Me.Status.Value = "COMPLETED") Then Me.Combo35.Value = DLookup("Unit", "Units", "PrimePro='" & Me.Combo33.Value & "' And "SubPro='" & Me.Subact.Value & "'") End If End Sub Please help....

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Using a stored integer as a cell reference

excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

VBA - do not grab header in range

excel,vba,excel-vba
I have code that looks for the header "CUTTING TOOL" using a .Find method. It loops through multiple files and multiple worksheets in the opening files. I have run into the problem that when it goes through multiple worksheets in one open file and the column is empty under the...

If statement and addition

excel,excel-2010
I am trying to look if a cell value in second sheet is not equal to blank and then check the value of other cell is lesser than 3 and further update the value to 1 or 0. 1st condition if g2 cell value present in second sheet is not...

Slow VBA macro writing in cells

excel,vba,excel-vba,ms-project
I have a VBA macro, that writes in data into a cleared out worksheet, but it's really slow! I'm instantiating Excel from a Project Professional. Set xlApp = New Excel.Application xlApp.ScreenUpdating = False Dim NewBook As Excel.WorkBook Dim ws As Excel.Worksheet Set NewBook = xlApp.Workbooks.Add() With NewBook .Title = "SomeData"...

Excel VB Listbox losing Value and Text Property

excel,excel-vba,properties,listbox
I'm trying to solve this problem along 2 days but I've not found the solution. I have a lot of listboxes on my excel and each of these listboxes are filled with different data, also I use these listboxes to change some filters at a pivot table using a VB...

Apache POI getRow() returns null and .createRow fails

java,excel,apache-poi
I have the following problem using Apache POI v3.12: I need to use a XLSX file with 49 rows [0..48] as a template, fill it's cells with data and write it out as a different file, so I can reuse the template again. What I am doing is approximately this:...

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

How to create nested tiles in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use the tile feature in Power View to view data much more quickly. However I haven't figured out a way to have nested tiles to further drill down into the relevant data. For example, I want a tile strip at the top of my view...

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

Excel build bar graphs with oddly formatted data

excel
I am currently working in Microsoft Excel 2011 on Mac OS X. I am given a large amount of data in different tables and need to make 2 variable bar graphs with the data. I understand that the usual way of orienting bar graphs in excel involves placing the data...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

parsing seconds from mm.ss.00 string

excel
I have a lot of elapsed time records in the form of mm.ss.00. For example if elpased time was 1min 44.72sec, data becomes 1.44.72. I want to convert these data into pure seconds data. Let me give some examples. |mm.ss.00 |seconds | |:--------------:|:-------------:| |1.44.72 |104.72 |2.5.32 |125.32 |0.59.12 |59.12 I...

Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format
I am in process of creating a macro that will save the current workbook, create a new outlook message and attach the file to the message. My macro does that but I can not format the text in the body of the email to my liking. Dim OutApp As Object...

Excel Search VBA macro

excel,vba,excel-vba
I have been given the task of searching through a large volume of data. The data is presented identically across around 50 worksheets. I need a macro which searches through all these sheets for specific values then copies certain cells to a table created in a new workbook. The macro...

Checking for null value (Empty Cell) in Excel file

c#,excel
I would like to count the number of empty cell in Excel sheet. I read the double values for all Excel sheet but I can't count the null values since the code below does not work probably. string path_connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + testbox_bath.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\""; OleDbConnection...

Compare two data sets in different sheet and print difference in other sheet using excel VBA?

excel,vba
I have two data sets in two different sheet Sheet1 is my Orginal ref and sheet2 is for comparison. sheet2 data should get compared by Sheet1 and print entire mismatched row of sheet2 and highlight the cells which has mismatch data and this difference should be printed with column header...

Range, Select, Change contents, Allignment or Offset?

excel,vba,excel-vba
I have a case at the moment where I am moving down the column with the names below and clicking on a macro, that then marks the indicator with a 35, a few columns down to the right. Due to the nature of the page, I am wanting to count...

Way to capture double click to open file?

excel,vba,excel-vba,add-in
Is there a way to have an Excel macro to check what file I double clicked on to open. When I open that file, the Add-Ins installed load first, then the file I clicked on loads. How can I write code inside one of my Add-Ins to check what the...

Retaining original table formatting after a 'pagebreak'

excel,excel-vba,table,formatting,page-break
So here's the finished product, a statement of accounts with a working statement table, and an ageing analysis: Everything works great. It basically populates itself row by row with data from another table. Here is the sample code: j = 21 'First row on the statement of accounts workbook For...

Conditional Formatting for every row

excel,excel-2010,conditional-formatting
I am trying to highlight the max value in each row of data to determine what year it falls in. Is there a simple way to apply it to the whole spreadsheet? The only way I can do it right now is by using the Format Painter on each individual...

adding variables into another variable vba

excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

Excel - How to concatenate 2 values to make a refference

excel,excel-formula,excel-2013
I have a long column of data (15000 values) that simplified looks like this: A B C D 1 lorem pellen Vestibulum 2 epsum tesque pretium 3 Morbi vel convallis 4 fermentum tellus nibh 5 Interdum molestie Vi .. 15000 Then I have a second table: A B C TYPE...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

java,excel,apache-poi,writing
I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

Ordering values from random list in Excel

excel
is there any possibility how to order values from cca 100 rows into table according to two criteria? Compare name and compare Category, or is it bad approach? Lets say i have a list of people: Name Category Value Carl A 10 Carl B 17 John A 11 Jane A...

Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic [duplicate]

excel,vba,excel-vba
This question already has an answer here: Detect whether Excel workbook is already open (using VBA) [closed] 7 answers I'm having a problem creating a condition. Please see pseudo code below. thanks in advance Check if File A.xls is open If File A.xls is Open Close File A.xls Else...

How to apply bold text style for a range of text inside a cell using Apache POI?

java,excel,apache-poi
How to make a range of text bold text style using Apache POI? Eg: Instead of applying style for the entire cell. I used to do this in vb.net with these lines of code: excellSheet.Range("C2").Value = "Priority: " + priority excellSheet.Range("C2").Characters(0, 8).Font.Bold = True But I can't find the way...

Is it possible to output to a csv file with multiple sheets?

java,excel,csv
I need to output data to a CSV file from Java, but in that csv file I hope to create multiple sheets so that data can be organized in a better way. After some googling, it seems this is not possible. A CSV file can only receive one-sheet data. Is...

Excel VBA - ShowAllData fail - Need to know if there is a filter

excel,vba,excel-vba,filter
I have automated a proper record input into the table that I use as a database, and when the table is filtered the input don't work. So I have code this to unfilter DataBase before every record input. Public Sub UnFilter_DB() Dim ActiveS As String, CurrScreenUpdate As Boolean CurrScreenUpdate =...

Counting values embedded in strings inside a column (Google Spreadsheets)

excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Finding position of a particular string containing cell in Excel

excel,vba
I am searching for a string using below code For x = 2 To lastrow If Sheets("sheet1").Cells(x, 3) = TFMODE Then ....... 'TFMODE is the string discussed 'This particular string "TFMODE" is randomly recurring throughout 'sheet in column 3. I need to know position for a particular string in sheet1...

C# code to add hyper link in excel sheet

c#,excel
Hi I am having an excel file containing multiple sheets. One of the sheets will contain the hyperlink for other sheets. While using the following code I have successfully added the link but when I click on the link it gives error **"Refrence not valid"** Code snippet: private void AddHyperLink(Workbook...

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

excel,vba,excel-vba
I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on...

How to Export an HTML table as an Excel while maintaining style and applying freeze pane

java,html,excel,apache-poi,jsoup
I am working on a project where an export to Excel functionality is required for a specific HTML table. The tables style needs to be maintained. Also, a metadata section needs to be added to the Excel (not present in the html table) and this section needs to be frozen....

Count amount of differences and return column name with count

excel,vba,excel-vba
i am finding the differences between 2 worksheets, the code is as follows: For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then mycell.Interior.Color = vbYellow difference = difference + 1 End If If mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then matches = matches + 1 End If When the...

Return #N/A error from ExcelDNA

excel,visual-studio-2010,excel-dna
I'm pretty new to excelDNA, so I may be missing something obvious. I'm trying to return a #N/A from an excelDNA UDF. The function I'm using (via Visual Studio 2010) is below: public static object returnError() { return ExcelDna.Integration.ExcelError.ExcelErrorNA; } When called from an Excel worksheet, this returns a #VALUE...

VBA - printing empty cells

excel,vba,excel-vba
I have code that takes information from under two specific column headers in opening files and prints them to a masterfile. One column is empty every few files and I need it to print empty cells to column 2 of my masterfile in the range of the filled cells of...

Deconstruct Excel Formula To Get Result Without Trying Values

excel,formula
I need to deconstruct Excel formulas so that I don't have to put values in to see what the result is. I want to put in a result and get a values. I know this is difficult given with multiple variables the answer could be different. I'm looking for more...