# 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.

205,777
questions

**0**

votes

**0**answers

12 views

### Use Worksheet Calculate to automaitcally sort values derived from formula

I want to sort rows automatically based on values in Column C, which are derived from a formula and will change. So far, I have:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ...

**0**

votes

**1**answer

13 views

### Excel, how to exclude one row from a look up range

I need to do a simple task. On column A I have some values, numbers, on column b I want to check if the value on column B is univocal on al column A. I tried to do this with an if and a Look Up, but ...

**0**

votes

**0**answers

4 views

### Rails 4 downloaded excel file using send_data method is giving format error

I am downloading data in excel format in my Rails 4 app using send_data method as mentioned below.
send_data collection.to_csv(col_sep: "\t"),
type: 'application/vnd.openxmlformats-...

**0**

votes

**1**answer

10 views

### Summation (Sigma) Formatting in Excel

I have an equation that I am trying to format in Excel:
Sigma (from 1 to n) where n=10 of (xi-xo)^2
I have 10 values for xi and xo=0.5 for this particular problem. How would I go about summing this?

**0**

votes

**0**answers

11 views

### Excel (VBA) Macro to replace cells in a column with Chinese characters based on list of translations

I am a VBA newbie here. Ideally, I would like to put together a macro that will scan a specific column for certain Chinese phrases and translate them per a list of translations.
I've started with ...

**0**

votes

**0**answers

23 views

### Unable to get date column to read from xlsx file in pandas using read_excel?

I have cells in Excel that are formatted as Date (see below):
I couldn't get them to be read (they were NaN) and so I used a converter to attempt to convert them to_datetime when read from ...

**0**

votes

**1**answer

16 views

### “User-defined type not defined” error for a simple code in excel-VBA

I wrote a simple code to open my browser from Excel-VBA with help of Selenium below; However, I got an error that says: "Compile Error: User-defined type not defined".
I tried to go to the references ...

**-3**

votes

**0**answers

20 views

### Excel Formula Assistance [on hold]

I currently have 3 columns with existing data. One is on the first tab of my excel file and the other 2 columns are on the second tab. I want the column on the first tab to find any matching values in ...

**0**

votes

**0**answers

19 views

### How to determine whether a project is completed, judging on sub tasks?

I have a list of project deliverable and within these there are sub tasks that make up each delibverable E.g. 1. Deliverable X is made up of 1.1,1.2,1.3.
The amount of sub tasks within each ...

**0**

votes

**0**answers

17 views

### Run a macro on filtered/hidden rows?

I'm new to the group/VBA so please forgive me if I'm not as concise as I should be.
I've got a macro that adds a formula to a range of cells. However, the range has filters and when I apply one and ...

**-1**

votes

**0**answers

14 views

### Excel: Allowing data to be added to a table only through the Data Entry Form with macro button [on hold]

I would like to create a table in excel in which data can only be added, changed or deleted through the use of the data entry form (the excel command 'Form').
I am able to use the Form to enter data ...

**-1**

votes

**0**answers

7 views

### Excel Javascript API: Apply font style to cell that contains a superscript

I am trying to apply several style attributes to a range. When the range contains a superscript character the style e.g. Font Size or Font Color is applied to the cell but not to the superscript ...

**-2**

votes

**0**answers

25 views

### excel vba userform code for calculate age [on hold]

im create userform containing two textboxes , TextBox1 & TextBox2 . when i enter date of birth in TextBox1 show me complete age and month in TextBox2 e.g. textbox1.value= 15/10/1984 then textbox2....

**0**

votes

**0**answers

30 views

### Using sort function on array of dynamic ranges in VBA?

Have an array of dynamic ranges that need to be sorted by the B1 position in each. I wrote some very long an inefficient code for a bubble sort that breaks occasionally and was wondering if the .Sort ...

**0**

votes

**0**answers

15 views

### How do I pass errors='coerce' to pd.to_datetime from pd.read_excel in Pandas?

I'm having an issue converting some date fields when using pd.read_excel
cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, skiprows = 6, nrows=2000, usecols = 'A:J', converters = {'...

**0**

votes

**2**answers

22 views

### Looking to extract a non-uniform part of a cell

I received some help from a peer building a formula but I'm lost on what to do next. I have tried a few things like adding another IFERROR clause to cover the missing data, but I can't seem to make it ...

**-1**

votes

**0**answers

17 views

### Match 2 columns and output with other columns to another worksheet

I am trying to compare and match column b (contains 300 rows) with column c (contains 500 rows) and sort while keeping Column A assigned to Column b data.
Thanks for your help.

**0**

votes

**0**answers

13 views

### Excel Filter, Multiple criteria, one column, both criteria true

I need to be able to filter the Type column twice and cannot figure out how to filter one column twice (two different conditions). I searched for the answer online but still cannot figure it out. I ...

**0**

votes

**2**answers

22 views

### How to count emails within an hourly range?

My team uses outlook to track completed work. All work needs to be completed within 48 hours of receipt and we need to keep strict track of it. I've managed to put together a function that counts the ...

**-2**

votes

**0**answers

14 views

### How to create different formula's for different filters

In the screenshot it shows a tab with negative vacation balance but these balances change for each person. Is there anyway to create a formula that applies to each filter. For example there would be ...

**0**

votes

**0**answers

15 views

### Table1[Col1] is not working in my INDEX and MATCH functions?

I have been trying to use the INDEX, VLOOKUP and MATCH functions to navigate through my table. However, I keep getting an error message saying: "There's a problem with this formula." Then it goes ...

**0**

votes

**0**answers

25 views

### Find column and then paste vba excel

I need to save some data by just clicking a button.
It should be easy but I dont know the syntax for vba.
Range("P2:P47").Copy
Sheets("All Data").Select
Range("J2").Select
Selection.PasteSpecial ...

**0**

votes

**1**answer

25 views

### Assistance with using AND + OR operators Together

I'm trying to reduce the amount of conditional formatting rules required to change a row colour.
Each row has a drop drop to select if the xray scanner being used is s1,s2,s3,s4 or s5. Once selected,...

**1**

vote

**1**answer

39 views

### Geting an error 1004 using OnAction in a shape

Ive been searching a lot of this error and couldnt find the solution,getting the following error:
Error 1004 defining Application or Object
Using the following code:
Sheets("sheet1").Shapes(...

**0**

votes

**0**answers

6 views

### Add multiple Params to action in a Drools rules decision tables using excel

I have a action in a Drools rules desicion table in excel file and this invoque a method in java. This method need 2 parameters for add the data in this method variables, but generate a error.
This ...

**0**

votes

**0**answers

15 views

### How to transpose Excel Pivot table

I have source table where I have every number category in separate column (something like already pivoted).
I would like to create pivot table, where division are in columns and number category in ...

**0**

votes

**1**answer

31 views

### Using “Text to Columns” macro on multiple columns

This image shows the initial state of the columns, in UK format. #value if I try to add 1 to it, False if I run ISNUMBER() on it, and True if I use text to column on entry to swap period & comma. ...

**0**

votes

**0**answers

19 views

### How to connect to AWS-RDS PostgresSQL through VBA?

I want to read data from AWS-RDS PostgreSQL into Excel, via VBA.
The solution should be compatible with Microsoft Office >= 2010 and preferably run "out of the box", i.e. not require the installation ...

**0**

votes

**0**answers

17 views

### How to import emails from outlook into excel document when it is a shared email inbox

I am new to writing code and am not sure how to go from the default inbox which is mine to a shared email inbox.
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Appointments")

**1**

vote

**0**answers

13 views

### How do I change the ZipSecureFile.setMinInflateRatio() in the ConvertToExcel Nifi Processor?

I am trying to ingest Excel files (.xlsx) and part of my Nifi flow (the second step) utilizes the preconfigured ConvertExcelToCSV Nifi processor, however, some of the Excel files set off the Zip Bomb ...

**-1**

votes

**0**answers

18 views

### Save DataGridView as an excelfile. C#

I have a datagridview I have created with a loop in windows forms. And I need to create a save function to save it as an excelfile, but I with help of a saveFileDiologe and I didn't find any tutorial ...

**0**

votes

**1**answer

20 views

### Looking to remove a space in one of the results while performing multiple mid searches on a column

I am trying to embed a SUBSTITUTE in my function, but I am not sure where to incorporate it. I am trying to extract just the text "Scrumactiviteiten" but in the source data sometimes a space will be ...

**0**

votes

**2**answers

42 views

### How do I count the number of times a specific time is between multiple time intervals (see example)

EDIT: Forgot to add the current formula (see bottom of post)
I have multiple columns that store time as interval values (e.g. 10PM stored as 22, 6AM stored as 06) - see image
I need to check if a ...

**0**

votes

**2**answers

43 views

### Is there a way to reference if cells have decimal point in vba?

I want to have create some code in VBA that will look through the cells in a worksheet in column B. If the code finds a cell value with a decimal (opposed to a whole number e.g. not 1, but 1.1) then ...

**0**

votes

**1**answer

26 views

### Geting error at TextBoxes property (one codeline)

I am geting an error at this codeline:
BUTTON_PRESSED = 1 'example
var = CInt(Sheets("sheet1").TextBoxes("Title_" & BUTTON_PRESSED).Text)
im triying to get the content of the textbox named ...

**1**

vote

**0**answers

23 views

### How to calculate an average grade from mix of number grades and letter grades whose values are determined via lookup table

Data Table
Lookup Table
I want to calculate the average grade score of each pupil using an array formula and without the use of helper columns. If all grade are numbers its simple enough to average ...

**1**

vote

**0**answers

32 views

### Turn all values into absolute values in VBA

I need to convert all values that have negative values into absolute values.
I'm working with the Kennedy Space Center to build a climatology based on 18 years worth of field mill data. Field ...

**0**

votes

**0**answers

21 views

### Excel : use averageif with formulas on the range

I have data that I get on a quarter basis (e.g. 2019-T2, 2019-T1, 2018-T4 and so on), data here.
I want to get yearly averages.
I have managed to do that in two steps :
Creating a year column ...

**0**

votes

**0**answers

29 views

### Issue with Intersect method

I've got a piece of VBA code for Excel Macros which prevents users to input text in the Russian language and it works, however, it throws out an error when copying/pasting a number of cells. I think ...

**0**

votes

**2**answers

26 views

### VB to Split rows keeping each row with the ID of column A in EXCEL

My first post as I am trying to tackle an Excel issue. If I can do this with a formula, I am open to that but all I have found thus far is VB solutions.
I am trying to take ID's in Column B and make ...

**0**

votes

**0**answers

28 views

### Run If Not after another If Not scenario

In my script After a Validation Item is Selected the code then selects the next cell and opens the validation list, what I am trying to accomplish is after that selection to then select the cell in ...

**1**

vote

**0**answers

24 views

### Autopopulating userform with vlookup function

I have set up a userform with an ID number at the top and below are list of boxes that match the rest of the database. When the ID number is selected I would like all the other boxes in the userform ...

**0**

votes

**1**answer

15 views

### VSTO Add-in Automatically Disabled Every Time Excel Opens (SAP IBP?)

We have written a C# Excel add-in using Visual Studio Tools for Office (VSTO). For whatever reason, users who have installed the SAP IBP Excel add-in now have our add-in disabled every time Excel ...

**-2**

votes

**0**answers

12 views

### 1:n relation in Excel Pivot Table

i have a table "Names" with people and their unique iphone and a 2nd table "Price" with the price of the iphones:
Names Iphone
joe iphone1
john iphone4
paul iphone1
jack iphone2
Iphone price
...

**-2**

votes

**0**answers

18 views

### Excel 3 Condition IF OR Statement [on hold]

I'd like to expand an IF OR statement to allow for 3 OR conditions. I'm concerned that if I combine a nested If with two conditions for OR that it makes the logic more rigid than I'd like. Is there ...

**0**

votes

**2**answers

22 views

### How to copy/paste a script from Excel/VBA to Rstudio

I´m trying to copy a R script from "Column 1" in Excel and paste it into Rstudio file "test2.R" and then run this code. I´m trying to do this using VBA. This is what I have now:
Public Sub RunRCode()
...

**-1**

votes

**0**answers

17 views

### get file location and compare timestamp [on hold]

I need 2 functions for VBA script:
1 - get file location by its fileName (bestly with wildcard) at certain folder including subfolders, for multiple resoult just use first matching file.
2 - compare ...

**1**

vote

**1**answer

32 views

### How to add cell value to certain part in Formula Bar

I have a formula that takes a value from a cell in different sheet.
='WEEK 42'!E12
Is it possible for 'WEEK 42' to be taken from cell value? Or even just a number 42 to be taken from cell value?

**0**

votes

**1**answer

22 views

### copy values in another sheet of excel from a changing value in excel

I have an excel which search a nearest value in H1 in a column then search the loaction of that value. Now I want to select 5 above and 5 below cells of the range and paste the value in another sheet. ...

**0**

votes

**1**answer

25 views

### Updating database record from excel userform

I am looking to update a database record using a userform. I have set up a userform with and ID number at the top and below are list of boxes that match the rest of the database. When the ID number is ...