Questions tagged [excel]

Only for questions on programming against Excel objects or files, or complex formula development. You may combine the Excel tag with VBA, VSTO, C#, VB.NET, PowerShell, OLE automation, and other programming related tags and questions if applicable. General help regarding MS Excel for single worksheet functions is available at Super User.

0
votes
0answers
6 views

Issue with a multiple criteria INDEX MATCH formula

so I used this array formula with INDEX MATCH: {=INDEX(ENTRIES!$F$4:$F$28;MATCH(C4&F4&G4;ENTRIES!$C$4:$C$28&ENTRIES!$G$4:$G$28&ENTRIES!$H$4:$H$28;0))} Here is the thing, I was trying ...
0
votes
0answers
4 views

Excel Calculating Incremental Sum / Total / Addition?

Excel formula that calculates incremental total based on the lot size? From this: To this: Qty is added based on the defined lot size so that each fruit is equal or above the demand.
0
votes
0answers
6 views

Number to words in VBA. Insert “and” for the last figure

I wanted to add the word "and" before the last figure but unable to do so. Eg: $101 (One Hundred and One) $110 (One Hundred and Ten) $111.50 (One Hundred Eleven and Fifty) Function ...
0
votes
0answers
12 views

it occurs an error “touch(): Unable to create file … because Invalid argument” when using Excel::download(new ProcedureExport, 'procedure.xlsx')

I use Laravel Excel. I run Excel::download(.....). then i get the error "touch(): Unable to create file C:\Users\duy.ldk\AppData\Local\Temp\laravel-excel-EBx85cfaCoWlGDZIvi1PXkpzASN7mpDn because ...
-1
votes
0answers
11 views

Logic to Add Supply Based on Multiple Conditions

I'm looking for a formula or VBA code to do following: Look for negative Supply in row A2 in Inventory table. If found then add the qty into Inventory (B3:D5) so that Supply is at or above 0 with ...
0
votes
0answers
12 views

How do I create an excel file with an existing macro using Python?

I created a macro using VBA and I want generate an excel file from python that is macro-enabled and has a button with this macro xlwings
0
votes
2answers
12 views

Format html table cell numeric value as text for Excel

Scenario: I am trying to export a html table to Excel file in a Web Application (website hosted online) for clients to download to their computers. All data exported as expected, except 1 problem. ...
0
votes
0answers
20 views

Runtime Error 91 - when testing code in Userform

Office 360, 64 bit, Excel. The subroutine, Private Sub Card_FindCardNumber_AfterUpdate() is a textbox (Card_FindCardNumber) that after I have entered in the number I am querying, the code should be ...
0
votes
0answers
17 views

How to split excel file based on Specific Column

I have example data like this (in Excel) Name Place A JKT B JKT C CGK D JKT E CGK F BBK G JKT H BBK I BBK J ...
0
votes
0answers
12 views

Using TRIMMEAN function with multiple conditions

I am having trouble putting together a formula that returns the TRIMMEAN value that fits multiple conditions. The 'Output' table (screenshot attached) needs to read the data in the 'Dataset' table (...
0
votes
0answers
17 views

VBA: Copying picture from another sheet problem; clipboard not clearing?

I have two worksheet; cal and sketch. In sketch, I have two pictures; Picture 1 and Picture 2. In worksheet cal, I have a cell with a dropdown list; contents of the list are 'Port' and 'Starboard'. I ...
0
votes
0answers
28 views

Remove a \r\n in the middle of a list

I'm trying to process an excel file and write the contents out in CSV. The data is not consistent and they \r\n could be in any cell. Because I'm writing out a list of data I can't convert it to a ...
0
votes
0answers
11 views

Sort Excel Pivot Table with multiple rows and one value colum

I am trying to sort an Excel Pivot table by a final value, and will try to explain the problem with the help of a simple example. Suppose I have two Rows (Bicycle, Colour) and one Value (Sales). I am ...
-1
votes
0answers
13 views

Is there a limit of google calandar events I can get with google api and excel-vba?

I'm using great code "GooCalendar" (https://sites.google.com/site/pageabbil/home/goocalendar) to download/upload events from/to my google calendar to/from my excel Dashboard. I have no issue to ...
0
votes
2answers
27 views

Read date from Excel file and get a delta with today's date

I have an excel file with some dates in it. They're formatted with dd-mm-yyyy. I would like to take each date in that column and get a delta of it with today's date. Foe example, if the Excel file ...
0
votes
0answers
29 views

Worksheet_Calculate or the Worksheet_Change for more speed and efficiency?

I have several cells that will be changed in very short time, more than 20. And I have several codes that will be executed (VBA) depending on the changes. In this case with regard to speed, efficiency ...
0
votes
1answer
17 views

How to insert future date/time from Excel into Powerpoint using VBA

I'm automating a letter that has to go out individually to 300+ people. The names are different for each letter but dates and times are also different. I am using Excel for the data and using VBA in ...
0
votes
0answers
22 views

Difference between storing range into array directly or by range.value or range.value2 [duplicate]

When I have a range stored into memory, say "A2:B10" as Range and I want to store this range into a dynamic array. Is there a difference between the two following methods? Pseudo code: Dim ws ...
0
votes
1answer
18 views

Use 1 column to “sort” duplicates from 2 other columns?

So here's the task i'm currently having. I have a spreadsheet of close to 74,000 rows and I need to cut down to show JUST rows that are duplicates. Column B has names and column D has phone numbers ...
0
votes
1answer
18 views

Unable to copy data from column to another workbook

I am very new to VBA, trying to write some code that will copy all data except for header from a column in source workbook and paste in a different column with header in another workbook. I can ...
1
vote
0answers
26 views

How to generate accumulated inventory plot based on week number in Excel vba

I would like to draw automatically accumulated inventory plot using Excel VBA 2016. I have inventory data for different year in the format of "20XXYY". Here "XX" indicates year and "YY" indicates ...
0
votes
0answers
22 views

Excel VBA - NextSibling.Innertext not working. “Object doesn't support this property”

Goal: I'm trying to extract the Bid prices for 200 securities from a website. My code is shown below for extracting only one security, I did not include the bigger loop for all 200 securities. ...
0
votes
0answers
10 views

load json array in Excel Sheet as a result [on hold]

I want to write a programm which is like the REST API Cient Add on from Firefox, but in Excel. Everything works fine. The Response from the Server is converting into the Excel. Now I am trying to ...
0
votes
1answer
23 views

Range variable (re)assignment

I have a range which is checked: Dim myrange myrange = Worksheets(1).Range("A1") I want to get the range of row +1 myrange = Worksheets(1).Range("A1").Offset(1, 0) But myrange =myrange.Offset(...
0
votes
1answer
22 views

Searching for Specific Column Headers in Excel File - Runtime Error 91

I am attempting to write some excel vba code that will process the content of certain columns of data. Given the worksheet has some level of dynamic change (columns added and removed from time to ...
0
votes
3answers
34 views

Change “,00 text” to “,00 | text” in excel

I'd like to replace some text in a string in excel into a different string. The data looks like: "text some more text 0,00 text some more text 0,00 text" The text could be anything, but does not ...
0
votes
0answers
9 views

Create a panel-data

I would like to create a panel data. My problem is the following. I want to keep track of 4 variables for more than 1000 cities, over the 1800-1921 time period. As of now, I have a dataframe in R ...
0
votes
1answer
26 views

VBA <> function is not working as expected

I am comparing two cells that contain numbers either 1, 2, 3, or 4. Inside a loop, I compare these integers from both cells. If they do not match (<>) then perform an Exit for next. VBA ignores my ...
0
votes
0answers
7 views

In Angular can I import only part of js-xlsx instead of using import * as XLSX from 'xlsx'

When I use import * as XLSX from 'xlsx' it increases main.js by 1.26MB, based on my source-map-explorer result below. Can I import only part of this library? Here is my usage of it: var workbook ...
0
votes
2answers
27 views

Sort row in excel by the lowest value (min value) + concatenate

I have a data array with column and row headers, I want to sort the rows in the array starting from the lowest value by concatenating the value with the column header name (tag). e.g Array example ...
0
votes
1answer
17 views

VLOOKUP against helper column with date formatted as text?

I'm using a vlookup against a table that has a generally-formatted column in the format "WORD NUMBER DATE" such as "Romeo 5M 06/16/2019". The reference is a helper column that I've created combining "...
0
votes
1answer
26 views

How can CountIf accept a structured reference column as criteria VBA-wise?

I'm trying to use structured references to the current columns the same as CountIf does for my UDF function. While =COUNTIF(Data[Team];Overview[Team]) works, my new function =CONCATENATEIF(...
0
votes
1answer
36 views

How do I read a closed excel using VBA?

I have a collection of .xls files in one folder. I wish to use VBA to extract values from these .xls while the files are closed.
0
votes
0answers
20 views

Spefic Rows Sum In Excel Using C#

I am trying to calculate the column sum by skipping specific rows with excel interop in C#. I am not able to figure out how to do that please help. I want to sum highlighted rows and skip the others ...
0
votes
0answers
10 views

Getting Excel file in spreadsheet workbook there is 'nill' BRAOFFICE Document

I want to read an Excel file from the Document directory in Swift iOS. Error Domain=NSCocoaErrorDomain Code=260 "The file “.rels” couldn’t be opened because there is no such file." UserInfo={...
-1
votes
1answer
25 views

How to delete a row if certain cells are blank in VBA

I am trying to delete an entire row if cells between a certain range are all blank. For example, for the range B6-H10, if the values from B6-H6 are blank, then delete row 6. It is worth noting that ...
0
votes
0answers
29 views

How to fix “ java.lang.NumberFormatException” error? [duplicate]

I'm a beginner in java and i'm trying to get a int value to use in some classes from a excel cell. this is my code, but i obtain this kind of error. Can someone tell me why? public class{ public ...
0
votes
0answers
23 views

Excel radar chart is not appearing (spider graph)

I have Excel sheets with data. My code imports another Excel sheet with coefficients. I would like to display these coefficients in a radar chart. But using my code below, the chart doesn't appear ...
0
votes
0answers
25 views

The spark driver has stopped unexpectedly and is restarting. Your notebook will be automatically reattached

I try to analyze a dataset of 500Mb in Databricks. These data are stored in Excel file. The first thing that I did was to install Spark Excel package com.crealytics.spark.excel from Maven (last ...
1
vote
0answers
31 views

Connection from VBA to Oracle DB

With setup from text below I successfully connected and retrieve data from DB. One time, while macro was executing, my PC (windows 10) lost the power and from that moment on I'm receiving the error ...
0
votes
0answers
15 views

Different String output when converting csv file to tab-delimited text file in excel

In my CSV file, there are many text records without any " character at the beginning or at the end of each record. but when I convert CSV file to tab-delimited text file in excel, I got a file that ...
0
votes
2answers
39 views

Counting the repeating values in comma-separated lists

My data in column A has comma-separated values (one/two digit numbers). I need a way to count the number of repeats in two consecutive rows. Example data: DATA Expected Results 1,2,3,4,...
0
votes
1answer
13 views

How to track when email is categorized and sent via vba in outlook?

I had such a problem: In my company we are using outlook as a main tool for daily work. The whole things starts when emploee assigned a category to mail and ends when this mail is sent. I would like ...
0
votes
1answer
14 views

Copy only visible cells from a Table column to one column separated by a comma in Excel VBA, Loop

Hello all smart people out there, I am working on a macro which allows me to copy values of a filtered column table to a single cell separated by a comma. I get my code to work for an unfiltered ...
0
votes
0answers
31 views

Error 1004 when calculating last used row

Why is 3rd line, beginning with "lastrow = ...", identified as error "1004" Run Time Error? Sub createNotifications() Dim i As Long, lastrow As Long lastrow = Sheet1.Cells(Rows.Count, 1)....
-3
votes
0answers
13 views

Is there a way to find and replace a specific line of HTML code based on an Excel value?

My job is to take an HTML template and create multiple versions based on information filled out in an Excel document. There are only a few lines of code that need to be replaced, and the data in Excel ...
-1
votes
2answers
30 views

In a formula, refer to a sheet by a cell value

First post, and very very newbie on VBA. I work out my problems but doing copy/paste from examples on such fine forums and sites like this one :-) Here's my problem: Current worksheet is CAPA , and ...
0
votes
0answers
16 views

How to change source data for Histogram charts

This generates an error message in the built-in Histogram charts but still changes the source data. If I change the chart type to other types, e.g. line, bar or pie charts it works fine and doesn't ...
-3
votes
1answer
69 views

Excel VBA macros: speeding up by turning off ScreenUpdating etc. sometimes freezes window

I am using this macro when I run Worksheet heavy macros: Option Explicit Sub Freeze(bol As Boolean) Select Case bol Case True Application.Calculation = ...
0
votes
1answer
21 views

Need a worksheet formula to detect if a delimiter in a cell should be disregarded among all others

I need to parse a string in a cell where there are multiple delimiters and I need to figure if one of them should be disregarded or not. Some of the Delimiters at some point in the string should be ...