Newest excel Questions

Q&A for professional and enthusiast programmers

How do i compare only the first 8 caracters in cells

In the macro below data in Sheet1 cells in column "X" is compared with data in column "A" on sheet2. If there is a match the entire corresponding row from sheet1 will be copied to sheet3. I need a ...

Excel SUMIFS Statement - Calculate profit for each month across multiple years

I've got three columns of data. Sell Date - Column G - Contains the day, month and year in dd/mm/yyy format. Month (calculated from sell date) - Column I - Contains month number Profit - Column M - ...

C# - Writing ata to existing template on Ms. Excel

I have windows form and after user filled up all data and click a button, all data that user inputted have to write to existing excel that already have a template inside and after that, filled excel ...

VS 2017 Document-level add-in build fails with com visible components

I have VS 2013 installed and I added 2017 RC. I made a document-level modified excel solution with some customisation in C# and this works fine. Then I added some VBA code in the spreadsheet and added ...

How to mass edit the format of cells in a CSV file - Excel 2013

Right now I have a CSV file with thousands of URLs. Each row contains a URL in this format: 5,google.com and this 1750,facebook.com. Each number is a URL count. Basically I want to change the format ...

Showing The Error “Exception from HRESULT: 0x800A03EC” While export to excel 2013 from C#

While Exporting from C# to Excel 2013, the following Error is showing Exception from HRESULT: 0x800A03EC The following code am using. to export the data from dataTable to Excel from c# ...

Importing excel file with specific range

I have many excel files with differnt amounts of columns and rows. I would like to import them into SAS, but I want to delete the first row, the first column and the two last columns of all of them. ...

Is there a way to apply tags to cells in Excel, and calculate with those tags?

I've never seen anything like this, but I thought of it and now I can't imagine why it's never been implemented before. Think about CSS. You can apply "tags" of sorts (classes) to any element in HTML,...

Excel | Searching table in different sheet and bringing value from column in that row

I am trying to lookup a table in one of my sheets. my table consists of three columns and an unlimited amount of rows. My table can be seen here: In my second sheet I wish to write a formula which ...

Is there a way to have an umbrella term for multiple values in my entire excel worksheet?

I have a massive excel sheet and a lot of times there are different names for the same thing I'm referring to. Is there a way to build an association map or umbrella term that I can refer to in ...

How to force an argument in an Excel UDF

I've created an excel UDF that accepts an array input. I want it only to allow an even number of items in the array. Here's the code: (it's only short so I'll post it all and that way you can have ...

VBA loop not working?

I have a workbook called Master with 2 sheets: Sheet 1 This sheet has a number value of '18' in cell I8 as shown above. Next I have my sheet 2 More on this sheet in a moment. I am trying to ...

Is there a way to upload multiple configurations at once to a test case in alm using vba/macro?

I have situation where-in I need to add multiple configurations to test cases in HP-ALM(Application Lifecycle Management). Adding 100s of configurations manually is mundane task. Is there a way to ...

Excel 2016 AddIn VSTO workbook project cannot be created

I'm trying to create Excel 2016 VSTO workbook project but i'm getting the following message: "The Project cannot be created." I have Visual Studio Professional 2015 version 14.0.24720.00 Update 1 ....

Save search results and return a list of addresses

I am running a search loop through a column via manual input and picking information of columns further right of the found item. I do not want to display the results immediately but at the end of the ...

vba and outputing data from hidden/Non-enabled multipages

I have a userform with various tabs on a multipage. I am using datepickers (Microsoft Date and Time Picker Control 6.0) and textboxes on the different tabs. The problem occurs when I want to get the ...

Getting VBA to click a button on an addin toolbar… CommandBars?

I have live data that needs to be updated every hour in excel, fed through the Reuters addin. This would usually be automatic but because I am feeding through a different kind of data, it will only be ...

Include source sheet name to an output sheet

I'm trying to consolidate multiple sheets into one sheet and add a new column for the final "Combined" sheet. The new sheet should have a column named "Source" with the sheet name from where the rows ...

How to export data from ListView to Excel and PDF (VBA)

I have data in a ListView and would like to export this data with column headers to a new Excel file and a PDF file. The LV is defined as follows: 'listview: | # | Col1 | Col2 | Col3 | ... ...

Dynamic referencing the UsedRange in VBA

I have a code that gets data from a sheet and creates a graph. In the source sheet, each column is a series, and the number of series may change. What my code does: it reads the used ranges so that ...

VBA - Add item to combobox from dynamically created listview

I need load ComboBox3 with items from ListView3 that is dynamicaly changed by value in ComboBox1. Is it possible to do something like this? Private Sub ComboBox1_Change() Call filterlist 'This line ...

Excel – locale-independent Number Format

I looking for a number format that is locale-independent. Let's say that I want that my Excel file use an a character as a thousand separator and a b character as a decimal separator on every ...

IF and VLOOKUP in worksheet change macro

I have a spreadsheet, small sample below, the actual is over 100 rows. I have a worksheet change macro for when the week number is changed in B3. If Target.Address = Range("$B$3").Address Then ...

Is it required to install MS excel on server to run Macros from a web application?

I have an Excel which has some macros. I need to execute those Macro from a MVC web application. Do I need to install MS Excel on server ?

Can access the sheets while macro running until i click menu button

Hi i still newbie in excel macro vba world, so i need some help with simple coding. i'm making menu with macro so by clicking it would go to the sheets and have a back to menu button but the problem ...

Conditional formatting based on data source

I have an excel (excel 2013) workbook where, on the main sheet, large amounts of data come from different sheets depending on "If" statements. Is there a way to format the data to show from which ...

Keep trailing zeros as is in .xlsx document created with PHPExcel

I'm creating an .xlsx document with PHPExcel and fill it from array. Data can be in various formats (string, integer, float, etc.). My problem is that for the float trailing zeros are dropped. For ...

VBA By ref error and naming rows

Im starting to get really frustrated. Sometimes it work and sometimes i just get error ByRef Argument type mismatch. what i also cant get my head around is a problem where im trying to name rows in ...

I have a stock list in excel

I have a stock list in excel. I'm using a formula =IF(E7=0,"-",D6+B7+C7-E7) which is fine as long as I enter a number in cell E7 but I need to get the calculation when I enter a value in B7 and/or C7. ...

Export to Excel with header text

I have code that is supposed to include header text when exporting to Excel. For i As Integer = 0 To DataGridView2.Rows.Count - 2 For j As Integer = 0 To DataGridView2.Columns.Count - 1 ' ...

Unable to assign data to Worksheet.TypeError: 'WriteOnlyWorksheet' object has no attribute '__getitem__'

I am a newbie to openpyxl for writing data to excel. I am actually developing a script that scrap data from a dynamic website and save it to excel. Following is the code for saving data to excel ...

Need to create shift schedule for employees

[enter image description here][1]I have 6 members including me and i need to create a shift plan where each person should get 2 week-off and on every night shift 2 persons and the person who got night ...

Copy worksheet from one workbook to another one using Openpyxl

I have a large amount of EXCEL files (i.e. 200) I would like to copy one specific worksheet from one workbook to another one. I have done some investigations and I couldn't find a way of doing it with ...

Highlight new/different items in a list Excel/LibreOffice Calc

I have a column of data (Version with list of reports), and I have a column of newer data (new version with mostly the same, but some different reports), and I would like to highlight the new/...

If value in Column E2 is “Purchase” then Sum C2:F2 else Substract C2:F2

I am trying to create an inventory sheet and trying to write a formula. Could anyone help me out. If value in Column E2 is “Purchase” then Sum C2 and F2 else Substract C2and F2 Need a formula for ...

Excel VBA loop folder and check if Excel file name is equal to worksheet

I'm in need of some enlightment. I'm trying to match a folder of Excel files with some sheets in an Excel workbook. So far, I'm able to read these Excel files names and corresponding sheets and copy ...

Excel VBA zooming range

If Target.Address = "$D$2:$F$861" Then ActiveWindow.Zoom = 100 [A5000] = "zoomed" ElseIf [A5000] = "zoomed" Then 'Otherwise set the zoom to original ActiveWindow.Zoom = 70 [A5000]...

Creating a Timesheet with at different rates

Scenario: I have an excel Timesheet which needs to calculate Standard Time 1x, Overtime 1.5x, Overtime 2x based on the following values: If the start time and finish time fall between 08:00 and 16:...

What is wrong with my conditional formatting formula?

Employee A Employee B Employee C January 1/1/2017 8 1/2/2017 1/3/2017 I am attempting to format a cell based on if a value is listed in the table above, ...

Complicated counting in excel

I have the following columns in Excel a b c 1 SUM 1st 2nd 2 0 2/2 2/4 3 0 1/1 3/4 b2 is defined as "=CONCATENATE(TRUNC(2);"/";TEXT(('Other sheet'!B2);0))" and similar to b3,c2,...

Excel Solver Password: what is it and where can it be acquired/bought?

I've been exploring VBA and excel and I've been writing a few basic codes around the Excel Solver. However robust the Excel Solver may appear, it's just not fast enough for my optimization problem (...

Copy cell value if length is < 10 into the last string of cell value above VBA

I've imported a text file into excel what happens is some texts had break into the next line i.e ColumnA -------------- 1313 Disneyland Dr, Anaheim, CA 92802 , USA '<---Copy/Cut cell value into ...

Vba Lookup value on sheet x if value like or similar to?

I have a workbook with 2 sheets. Sheet1: Column B Column C Column D Column E Dairy Crest Ltd Milk Farm Tuna Family Guiness Sheet 2: Column A ...

VBA Copy only non blank cells

does anyone one how to instead of static range: '~~> Copy the range wsI.Range("A1:K50").Copy To change it to dynamic where A1:A50 is calculated depending on non-blank cells. So if Sheet1 ...

Loop through the set of values in Excel

capture 1 is constant value for country and city Capture 2 is the list of values to loop through capture 3 is the output file needed . please let me know how to achieve the same through VBA in ...

detect cell value in different excel sheets that contains different value using vba excel

Based on the code below, I want this code to detect the word "Oranges", "Oranges1", "Oranges 2" etc but all I can do now is only to detect Oranges. It became more complicated for me when I want to ...

Sorting files in a folder using VBA

I have a code that reads all the files in a folder, paste the file names in a worksheet and output the number of files into a message box. Problem: for some reason, when I run the code, it may get ...

Excel VBA - Using the range function within a range function

There are 2 things to note here. The first: I am using a For loop to step through each row. The second: I don't know which is the last column with data in each row and hence I am trying to use the ...

VBA formula does not work properly

Hello I want to implement a formula in my VBA code. It should check if all values in a column are the same. Dim intBB2 As Integer Dim LastRow As Long Dim ISINcheck As String ISINcheck = ...

How to change excel formulae of xlsx to be compatible with xls with least effort?

I have prepared an excel workbook in latest version saved as .xlsx I need it to run on an older PC using an older excel (.xls) Some of the compatibility issues it reports deal with: Table References ...
Translating... 0%