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.

Filter by
Sorted by
Tagged with
0
votes
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
2answers
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
0answers
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
0answers
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
2answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
2answers
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
2answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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 ...