289,563 questions
1
vote
0
answers
32
views
VBA Can’t refresh power query SQL query if the Macro was started by VBS script
I created a report to refresh the combine my SQL power query by VBA. It will be started by a VBS script in window scheduler at specific time of the day.
However the refresh sometimes works and ...
0
votes
0
answers
48
views
Excel VBA Problem copying/pasting rows from one workbook into another based on criteria
I'm trying to create a script that will pull specific rows from one sheet, and then paste then into a new sheet on the workbook that's running the code. It currently works to search through my list, ...
- 1
0
votes
2
answers
72
views
Reducing indentation of specific lines within 1 cell based on contents within line
Incoming with another VBA question.
I'm currently have data within a column that has many line breaks, but certain lines need to be only indented 2 spaces whereas certain lines need to be indented by ...
- 21
Tooling
0
votes
5
replies
38
views
What tool can be used to read Excel online checkbox response?
I have multiple excel online files with question and responses in them but I can't read the checkbox responses of yes and no in python or ssis.
I want to export them to BI tool and aggregate the ...
- 9
0
votes
1
answer
66
views
Excel Formula - Calculate cost using height and width of glass in mm based on (minimum sqm cost value £270)
I am trying to use an Excel Formula to calculate the cost using the height and width of glass in millimeters, dividing the result by 1000 and then multiplying it by the minimum square meter cost value ...
- 13
0
votes
1
answer
109
views
Why is my Excel VBA macro worksheet change event is not firing? [closed]
I am very new to coding and I'm trying to write a code for a worksheet change event, but it's not doing anything. I currently have a drop-down list of students in each individual cells AR5:AR104 (in a ...
Advice
0
votes
3
replies
119
views
Localize cell depending on different column
First I would like to apologize for the unclearness of my previous post. Now, I need a code that I can use for locating cells which will be filled with values from a userform. So for example ...
- 21
2
votes
1
answer
131
views
Trying to set a button that establishes a macro for other buttons (and excludes other buttons not in a specified range)
I'm trying to get a button that establishes a macro for other buttons, but not itself.
So far, what I've got is VBA that does successfully change the macro for buttons, but it ignores range parameters ...
- 33
Advice
2
votes
2
replies
93
views
Non-recursive method of calculating future value of cashflows at irregular dates
Suppose I have irregular dates in column A, cashflows in columns B and let's say an 8% rate. I am trying to produce a formula to calculate the future value of these cashflows, taking into account the ...
- 165
1
vote
0
answers
107
views
Excel & Office Script - Button "add in workbook" not visible - Impossible to share script or to attach it to a workbook
I wrote a simple Office Script to process data in a Excel workbook. (it's my first use of Office Script)
Code is simple and the script run without error and do what I was expecting.
But impossible to ...
- 31
0
votes
2
answers
171
views
Importing from Outlook summary to Excel
Here is a tiny sample from my Outlook summary, that actually has 941 incoming emails from 50 people (names, etc., changed to protect the guilty!).
From
Subject
Received
Size
Veronica
Topic A
15/10/...
- 21
0
votes
1
answer
147
views
Testing for an Open/Locked file
I am trying to implement, in Excel VBA, some non-destructive file access pre-checks (in a loop) before carrying out multi-file (wildcard) operations, eg, delete, copy, move, in order to avoid the ...
- 93
1
vote
1
answer
102
views
Bizzare "kernel has died" error in pandas df.to_excel() caused by geopandas
When pandas.read_excel(), df.to_excel(), geopandas.read_file() and gdf.to_file() are called in a certain order in different environments, pd.read_excel() sometimes causes "Kernel has died" ...
- 293
1
vote
2
answers
141
views
List all words from a cell as a list in a ComboBox (userform)
I have cells with a sentence in each (text without line breaks). Now, when an existing Userform is opened, I would like each word of the text in the cell (in the last row) to be listed in a Combobox-...
- 21
-1
votes
0
answers
50
views
Referencing a cell in a sheet using a cell in the current sheet [duplicate]
I am creating a financial overview sheet. I have one sheet that is the full overview with rows named (using the defined names feature) after the month and the year (e,g. June.25) and separate sheets ...
- 17
Advice
0
votes
2
replies
43
views
Spool failure that breaks up the excel due to few columns that contain paragraphs worth of data
So I have been tasked to automate numerous reports which i succeeded mostly apart from this one case, I tried many recommendations from chatgpt but most were busted, the broken excel file started ...
1
vote
1
answer
112
views
Formula To Not Display Data In A Cell Until Data Has Been Entered In Two Other Cells
I have a simple lookup table which requires two input values, LEGACY STORE and LEGACY REF
In columns C, D and E, I have a formula to match on a lookup value and return a corresponding mapped value:
=...
0
votes
1
answer
82
views
Macro to take an image from a spreadsheet and copy it into the body of an email (possibly attaching it too)
I have a macro that emails everyone from a client list but I want to include my company logo as a header on the email. The logo is on sheet 11 of the workbook and is named "Picture 3".
I ...
-2
votes
0
answers
65
views
How to dynamically extract multiple tables with varying formats from Excel (.xlsx) files? [closed]
I hope you're doing great.
I'm facing a situation where I need to dynamically extract data from Excel (.xlsx) files that contain:
Multiple tables per sheet
Different table formats
Inconsistent ...
1
vote
0
answers
95
views
Why is wb_add_comment throwing an error when trying to add comments across multiple columns using openxlsx2? [duplicate]
I have a dataframe which I am trying to export to Excel with formatting and comments. To do this I have created a custom function using functions in the `openxlsx2' package to add color fill and/or ...
- 456
0
votes
0
answers
134
views
Anchor a Column Reference in VBA, Cells method
In this VBA formula, I need to anchor the variable wsPrice_Col2's column so when I add other columns with formulas, this column reference stays the same. Currently the wsPrice_Col2 column reference ...
- 3
1
vote
1
answer
91
views
VBA Power Query refresh
I need to refresh the PQ data before copying and pasting reports.
When I launch PQ for the first time, it only generates one report. This is incorrect because reports should be created based on the ...
Advice
0
votes
1
replies
64
views
Multiple samples and transformations in one power query model
I'm running power query in Excel to combine one set of files into one 'final table'. I have an existing power query model that completes this task. For reference, the files in the first set are all ....
- 23
1
vote
1
answer
119
views
Why does this function throw #VALUE errors?
This code is for a function, "Compound," that tries to return the interest accumulated for a starting investment (P) on a specific date (when), using a specific interest rate (r), and a ...
- 77
0
votes
0
answers
83
views
Paste simple text from clipboard into multiple Excel cells at once, in one step, seems impossible? [closed]
I want to do something straightforward, but it seems impossible. I want to copy a simple text string (such as "GROUP1") from another program or text file, then paste it from the clipboard ...
- 353
0
votes
0
answers
71
views
Excel move VBA WinHttpRequest to Script Fetch fails Failed to fetch at self.fetch
We use an API call to a custom site run by another company. The calls require a token to be generated first and passed along. The following code to generate a token works with no problem in VBA:
Dim ...
1
vote
2
answers
101
views
Excel Lookup Formula not Returning Correctly
I'm looking to use values on another mechanical page that correspond to columns on the table, look in that row for that student and return 1 of 3 values if it is blank or not.
Sample
Problem is right ...
- 13
0
votes
1
answer
42
views
Setting Excel_Application.Calculation property (via Delphi & OLE)
I am trying to read and set the calculation mode in Excel. I have
type
TXlCalculation = type LongWord;
const
xlCalculationAutomatic:TXlCalculation = TXlCalculation(-4105);
xlCalculationManual:...
- 9
-1
votes
0
answers
104
views
Type Mismatch Error When Providing String Value to CustomOrder Parameter in SortFields.Add Method If CustomOrder Value Not Wrapped In CStr() [duplicate]
I have a section of Excel VBA code that worked during prior attempts, but suddenly threw a Type Mismatch error. I confirmed the error was caused by the CustomOrder parameter by removing it and ...
- 1
2
votes
1
answer
110
views
Hiding columns in Excel based on cell value using VBA macro doesn't work if cell value is result of formula
I know very little/next to nothing about Excel macros. Just so you know where I'm coming from.
I tried searching for a way to hide/unhide columns based on the text value of a reference cell, and found ...
- 23
2
votes
1
answer
145
views
How to adjust Excel PMT/IPMT formula for changing interest rates in amortization schedule
I am building a loan amortization schedule in Excel for a 30‑year (360 months) home loan in Australia, where the interest rate is not fixed, but variable across the term (Excel loan amortization ...
- 37.6k
Advice
0
votes
3
replies
147
views
Excel - Mathematical calculations: how to find the best matching volume?
I have one worksheet with a few columns:
Product name
part name
order of part in product
Volume A
Volume B
Difference vol A and B
Dif %
One part can be used for many products. All products are ...
- 77
2
votes
3
answers
125
views
How to use VBA to populate one column based on another column in excel
I have an excel sheet with a bunch of vehicles in a column, and I want to populate a new column with the "type" of vehicle.
Here's a quick screenshot.
Or to save you a click it's like this:
...
0
votes
1
answer
149
views
Can't create tables automatically in Word document from Excel VBA
I'm trying to create a Word document from an Excel workbook. It loops through a number of sheets creating one page in Word per sheet in Excel. Creating basic text works just fine. It's when I try to ...
3
votes
3
answers
125
views
How to fix "cannot run the macro" error for a macro in an addin
I am having trouble launching subroutines from an add-in via the Quick Access Toolbar (QAT). Over the past year or so, with various versions of the add-in, I have had some successes and many failures, ...
- 31
-1
votes
0
answers
127
views
Applying formula conditionally on an Excel column
I have an Excel with columns INDEX, NAME, TYPE, UNIT, ID, MAP.
"MAP" column has TRUE and FALSE values as shown in the screenshot.
"ID" column has a drop down. It is populated only ...
0
votes
2
answers
140
views
Use Stream to connect to MS-Word docs in SharePoint and copy contents to local MS-Word doc
I have MS-Word Document templates that are stored in SharePoint. I need to write a sub within an Excel file to reach into SharePoint, copy the contents of the word document(s), and save to a local ...
0
votes
3
answers
188
views
Split Column by Delimiter and Keep Adjacent Column Values Associated
I have a list like the one shown below:
Owner
Alone?
Mickey
Yes
Goofy
Yes
Pluto
Yes
Pluto / Mickey
No
Minnie
Yes
Daffy
Yes
Minnie / Goofy
No
Donald
Yes
In Excel, how can I make it that each name has ...
- 35
1
vote
1
answer
132
views
Using AND Function to Aggregate Results from Dynamic Evaluate Named Formula on Multiple Rows
Problem
Trying to create a formula that gets me to the result of cell G3 without the intermediary step of having 5 formulas (i.e. E3:E8) for each row of my Data Set
excel
E3=EVAL_FORMULA(CONCAT(...
- 109
1
vote
1
answer
66
views
List Named Ranges in Active Sheet
I want a MsgBox that displays the names of the "Named Ranges" in the active worksheet, along with the "RefersTo" field.
I created a macro that I thought would work, but when I ...
- 11
Advice
0
votes
4
replies
64
views
Make PPT with slides from selected fields of Excel sheet (using AI tools)
I have an Excel sheet with several fields. I want to generate a PPT with one slide per row (having selected multiple rows) in the Excel sheet.
How can I do this using some AI tools (free versions only)...
- 4,717
7
votes
2
answers
235
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), ...
- 93
0
votes
1
answer
90
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
150
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
164
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
63
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
129
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
68
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
99
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&...
