289,593 questions
0
votes
0
answers
18
views
Bug in Excel VBA Format function?
I've been using the built-in Format() function successfully in Excel VBA for years. However, I have just come across a situation that would appear to be a bug. When the input value is Empty (Variant), ...
- 21
-3
votes
0
answers
39
views
Python solution to extract all tables PDFs and save each table to its own Excel sheet [closed]
I’m working with around multiple PDF files (all in English, mostly digital). Each PDF contains multiple tables. Some have 5 tables, others have 10–20 tables scattered across different pages.
I need a ...
0
votes
0
answers
52
views
Evaluating Text String as Formulas / Dynamic Look Up with Multiple Criteria: <>, =, >, >=, <, <= as Sign Inputs and an Input Value Cell
I have a workbook with 2 tabs: an Override tab and a Calculation Detail tab. I am trying to create a formula that returns "Y" or "N" in the parameter_# field of the Calculation ...
- 89
0
votes
0
answers
30
views
Excel dynamic arrays not able to be multiplied together
I have two arrays that won't multiply together, I just get a single zero. I would expect the result to be an array.
Is there some behaviour of dynamic arrays that I don't understand here? The arrays ...
- 35
0
votes
1
answer
49
views
BeautifulSoup - Extracting content blocks after specific subheadings within a larger section, ignoring document introduction
I am scraping the Dead by Daylight Fandom wiki (specifically TOME pages, e.g., https://deadbydaylight.fandom.com/wiki/Tome_1_-_Awakening) to extract memory logs.
The goal is to extract the Memory ...
Advice
0
votes
20
replies
270
views
Date function vba how to
I have this code that works great except for when the end of the week comes up I have to change 8 to 15 or vise versa in order to get to the correct workbook. Is there another way? This code is to ...
- 9
2
votes
3
answers
131
views
How to add cells from different pages to a cell
I have a spreadsheet with 31 pages that keeps track of my bowling teams scores, points won, etc.
Each week (30 weeks total) is a separate page. I have a summary page in that spreadsheet that keeps ...
- 23
0
votes
0
answers
139
views
As part of a function, how could I create a variable number of sums one inside another based on a parameter?
I'm currently struggling on an assignment (probability class) which require creating a function (VBA excel) that contains a variable number of sums one inside the other based on a parameter. I'm not ...
- 1
0
votes
2
answers
52
views
Incorrect currency being displayed using Stock Data Type
The inbuilt stock data type in Excel is not correctly displaying the price of the stock. The issue is with stocks on the LSE as they are reported in pence and not dollars, Excel is reporting it as ...
- 1
1
vote
2
answers
107
views
Searching for a range of dates in Excel Using VBA
I have a problem in making a search for a range of dates with VBA. Using the start date as the default search in the date range and when find the same date start to count (x) in the range between the ...
- 23
1
vote
1
answer
60
views
Excel Conditional formatting tie breaking
I have a spreadsheet of prices from a selection of suppliers, with one supplier per column.
// Conditional Format code
=D2=MIN($D2:$K2)
I have used conditional formatting to highlight the cheapest ...
- 11
0
votes
3
answers
94
views
Nested Or statement IN Excel
I am looking for help with excel syntax. I am looking for if cell B4 = "Jan", "Feb" or "March" it returns Q1, if B4 = "April" ,"May" or "June&...
Advice
1
vote
3
replies
130
views
Best way to clean awkward Excel column headers in python/pandas?
I've got four years of daily school attendance data spread across 40+ Excel files (one for each month) and the sheets are set up in a truly annoying fashion, with each date in one merged cell in the ...
0
votes
1
answer
101
views
insert row with total where criteria count ends
i have excel sheet with 5 columns. i used following code for sorting. it works fine
lrow As Long
lrow = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Sheet2.Range("A3:E&...
- 11
2
votes
2
answers
70
views
How can I merge 2 arrays into a 3rd array when all 3 are derived using the FILTER function?
I'm building a workbook designed to isolate three different data sets from a single data source using Excel's FILTER function.
What I'm trying to do is combine filters 1 and 2, then subtract their ...
- 21
4
votes
3
answers
81
views
Excel array formula using if() with two rows from spilled array source
I have set of data that I am pulling from another sheet with filter() function, and then I'd like to use array formula if possible (with # operator) to have formula spill dynamically for as much rows ...
- 215
0
votes
1
answer
105
views
Why is my exported Google sheets adding trailing zero to character when read by read_excel in R?
I have a data sheet that I am entering in Google Sheets and importing into R as an .xslx file. One of the columns in the datasheet is a character variable representing the specimen numbers of each ...
- 424
Advice
0
votes
3
replies
77
views
Prevent Duplicate Sheets when Adding using VBA
I am adding new sheets to a workbook using the value of a column.
In the column there are duplicate values. I want to skip a duplicate value in my loop.
- 175
Best practices
0
votes
2
replies
118
views
What is standard practice for handling VBA fixed-length string data in binary files?
I'm quite frustrated trying to write to a binary file in Excel VBA. The issue is with fixed-length string data in a Type structure. (Note that my binary file requirements are that fixed-length string ...
- 153
0
votes
1
answer
114
views
VBA Code to get a formula returned in column2 when column1 is not empty, error in my formula
I have the following VBA code, but one line is giving an error:
rng(i, 4).Formula = "=IF(LEFT(A" & (i + 1) & ",2)="RN","RN",IF(LEFT(A" & (i + 1) &...
0
votes
1
answer
172
views
Why am I getting an Out of Memory error with this specific VBA code?
I have Power Query table in an Excel spreadsheet that filters a different table for a specific Index number using a List Box of Client Names.
The first table has 3 columns:
Index
Attribute
Value
1
...
3
votes
1
answer
101
views
Trying to populate a list from another sheet by a single reference number
I am working on a handy reference sheet for my DnD Games, that includes 10 pre-planned encounters of varying difficulty that I want to reference on an Initiative Sheet by referencing the Encounter ...
1
vote
1
answer
109
views
Pandas converts Excel strings like ‘2004E205’ to scientific notation — how to prevent this
How can I handle string values that contain patterns like xxxE205 (e.g., 2004E205), which are used as unique codes in my company? I explicitly read the column as a string in pandas, but values ...
- 11
2
votes
3
answers
196
views
Insert and Paste Values to a protected sheet
This Excel VBA code is supposed to copy the entries from Sheet "Entry" then Insert & Paste only Values in Sheet "List".
Sheet List has a password to prevent editing.
Sub ...
- 39
Best practices
3
votes
19
replies
288
views
SpellNumber Simplified Using LAMBDA() function
Here is the custom function to spell number to words using LAMBDA(). I would appreciate any improvement of the function or simplification if possible. Also suggest best practice for the function.
=...
- 38.1k
0
votes
0
answers
100
views
Importing data: VBA run-time error 1004 Application-defined or object-defined error
I am trying to import a table from one workbook to another, but I want to add the table with each import to the next empty cell (i.e. don't want to overwrite existing data in the target workbook). I ...
0
votes
1
answer
72
views
I need to stop a tab from being exported. Is there a Workbook BeforeExport similar to BeforePrint?
I have managed to stop a tab from being printed using the BeforePrint... I now need to prevent the File|Export from firing for the same tab. Is there something like the code below to stop FILE | ...
Best practices
0
votes
4
replies
113
views
Best way to extract a VBA Date out of a byte array and turn it back into a valid VBA Date?
I have confirmed that the following VBA code works just fine.
Dim mydate As Date
mydate = Date
Open "C:\Windows\Temp\dlctest.data" For Binary Access Write As #1
Put #1, , mydate
Close #1
...
- 153
0
votes
1
answer
111
views
Excel INDEX MATCH with single row and multiple columns reference
I need to find openings in a group of people's schedules because I can't merge them easily with any native web program we use the calendars in. I will be looking at each person's calendar at a time ...
0
votes
0
answers
23
views
Aspose.Cells (.NET) (25.5.0): Error Logging
I was wondering is there a way to configure error logging for Aspose.Cells (.NET). The version I am using is, 25.5.0.
I am facing one issue with an excel workbook that has more than 10 excel sheets ...
- 1
1
vote
2
answers
97
views
Return each unique combination from 2 columns and return other information from the same row
I have a tool I am working on using the latest version of Excel (whatever it is) and I have a table which looks like this:
I want to take each unique combination of values from the Code and POBox ...
- 35
1
vote
2
answers
107
views
How to check if two cells in the same column contain matching text in Excel?
Is there an Excel formula that can detect whether two cells in the same column contain similar or partially matching text?
For example:
F3 contains: "regulation BI, how to stop exploitation"...
1
vote
2
answers
106
views
Problem creating cell output from Date using VBA script in Excel
I'm trying to use VBA to insert a new row and then create a reference using the date in the format yyyymmdd followed by a hyphen and then 1.
So for example, today I would like the output to read ...
- 11
Advice
0
votes
8
replies
141
views
Excel conditional formatting: how to format entire RELATIVE columns based on single row value
My question is quite straightforward - or at least, it should be!
I have an Excel sheet with dates and weekdays laid out across the top three rows. I want to highlight all columns in a particular ...
- 1,516
Advice
0
votes
1
replies
119
views
I want to prevent fill and drag in Excel
I want a VBA code to prevent fill and drag in an Excel sheet without hiding the fill and drag cruiser. and without preventing them from advanced settings.
In addition, I want this code to be localized ...
Tooling
1
vote
3
replies
97
views
Convert complex pdf to an excel
I'm currently searching for a solution to read a pdf and convert it to an excel.
For now I found the "tabula-py" library, which seems to be good. But I'm not quite sure because the pdf has ...
-3
votes
1
answer
216
views
Check if Map Chart drawn successfully or not
1) The following code is able to draw a Map Chart successfully.
Public Sub Macro1()
For i = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(i).Delete
Next i
...
4
votes
1
answer
95
views
Export pandas.DataFrame.column.name attribute during pd.to_excel() export
a = np.array(["foo", "foo", "foo", "foo", "bar", "bar",
"bar", "bar", "foo", "foo", &...
- 149
0
votes
1
answer
50
views
How to group time values by hour (not minute) in Excel PivotTable using VBA?
I'm creating a PivotTable in Excel using VBA. One of the fields is a time value (formatted as "hh:mm:ss"), and I want the row labels to show each hour (e.g., 00:00, 01:00, 02:00), not every ...
1
vote
1
answer
145
views
Excel VBA BeforeDoubleClick not working after other code is run
When I first open up the Excel file, as well as after I click on a button to import additional sheets into the file, the BeforeDoubleClick event works (it adds 'ticks' to cells so the user can see how ...
- 159
3
votes
1
answer
108
views
Why does an adaptive Image control in a VBA UserForm not render as expected for different diaplay scales?
I have a simple UserForm to prototype the concept of loading different sizes of the same image based on the Windows display scale that the user sets. For this example, I'm just testing with 100% scale ...
- 3,049
1
vote
2
answers
153
views
VBA Excel trying to get a variable defined as Range into Cells format
I'm probably missing something very simple here but, I have cobbled together the following code VBA code in Excel,, mainly from answers I've seen here and this runs OK. However what I would like to do,...
- 33
1
vote
1
answer
84
views
"File not found" copying worksheets into a new workbook
I'm working with a macro designed to copy selected sheets into a new workbook, replace their formulae with values, then resave the file in the same folder as the macro workbook. For the most part, the ...
- 1,244
2
votes
1
answer
64
views
Exporting a VBA module from a SharePoint file
I am trying to export a VBA module from one workbook and import it into another:
Dim ProofName As Variant
Dim sDate As String
Dim CashShN As Variant
Dim wbkSource As Workbook
Dim wbkTarget As Workbook
...
-4
votes
2
answers
176
views
Move Labor Cost Formula from Sheets to Excel
Problem
I have created quite a convoluted formula in Google Sheets that Sheets doesn't seem powerful enough to handle for more than ~80 rows of data, so I wanted to try moving it over to Excel. Seems ...
- 27
3
votes
1
answer
87
views
Change the font color of data labels in a Sunburst chart in Excel
I’m trying to change the font color of the data labels in a Sunburst chart in Excel using VBA.
So far, I’ve tried several approaches over the past two days, but I can’t seem to access or modify the ...
1
vote
1
answer
99
views
How can I Kill Child Excel Process [duplicate]
I am using Microsoft.Office.Interop.Excel.Application
My MyMainFunction() function is executed.
If an unexpected forced termination (a crash) occurs immediately after excelApp = new Microsoft.Office....
- 13
Advice
0
votes
7
replies
122
views
VBA- getting data using partial names
I am working with two different workbooks. WB1=Destination data, WB2=Source. I am trying to do a lookup and match between the wb, using wb2 to find the match in WB1. Once the match is found, copy ...
- 9
1
vote
2
answers
107
views
Excel adding unicode symbol from r csv output
Excel is adding a unicode character to a summary file I save from r as a .csv, where it adds "¬" in front of "±". Is there a way to edit the r script to prevent this?
cola <- c(&...
- 97
1
vote
1
answer
72
views
Excel VBA, is it possible to select column/row range within a table to paste information into, and use torow for it?
I am trying to get data from my data imput sheet into my record keeping table, so I am trying to do VBA code to
1. create new row
2. paste the data in there as value and not formula
I created ...
- 13