logo
down
shadow

EXCEL QUESTIONS

Simple form of looping formula that takes a long time
Simple form of looping formula that takes a long time
I wish this helpful for you Writing to a large number of cells, one cell at a time, is very inefficient. Instead, first create a temporary array, and write the results to the array. Then, transfer the contents of the array to your worksheet all at on
TAG : excel
Date : January 12 2021, 09:11 PM , By : Adrian Codrington
Setting proper case to a range using xlUp
Setting proper case to a range using xlUp
I hope this helps you . I have the code below that I would like to use to set the proper casing for a range that can be 20 records or can be 500 records as the data changes daily. The problem is that takes long because it is evaluating blank records.
TAG : excel
Date : January 12 2021, 09:11 PM , By : Fernando
Circular Reference in Excel VBA
Circular Reference in Excel VBA
To fix this issue You don't perform actions with functions. You shouldn't try to modify cells or anything. (In your code, every time the function is recalculated, it would be modifying whatever cell you have selected.)You just need to set the value t
TAG : excel
Date : January 12 2021, 08:33 AM , By : Laques
Worksheet without Workbook or Application?
Worksheet without Workbook or Application?
I hope this helps . No, you need a Workbook object to nest your Worksheet object. But you can create a Workbook without having an Excel.Application.
TAG : excel
Date : January 12 2021, 08:33 AM , By : Zelos
Visual Basic for Applications (VBA) Count Unique Cells
Visual Basic for Applications (VBA) Count Unique Cells
help you fix your problem I'm attempting to make a Visual Basic Macro to count unique items in a row without doing the copy and pasting and data remove duplicates. , The problem is you are setting i = 0 then saying
TAG : excel
Date : January 12 2021, 08:33 AM , By : n1ckless_id
trigger filldown formula based on cell of another worksheet
trigger filldown formula based on cell of another worksheet
To fix the issue you can do I have four worksheets and I write one formula in cell F2 which trigger a filldown for all the rest. I already have the trigger private sub to call my procedure that works. Is there a way to make this sub more efficient? ,
TAG : excel
Date : January 12 2021, 08:33 AM , By : woxorz
How to copy array while removing last character and obtain result as double
How to copy array while removing last character and obtain result as double
To fix the issue you can do There may be another better way, but I find .Evaluate an interesting option which in this case could work for you.:
TAG : excel
Date : January 12 2021, 01:40 AM , By : Hibame
How to pass an Excel worksheet name to an Access subroutine?
How to pass an Excel worksheet name to an Access subroutine?
I hope this helps you . I'm trying to loop through all the worksheets in an Excel file using Access VBA. , Why does it not work?
TAG : excel
Date : January 11 2021, 05:14 PM , By : chudq7
Excel VBA - cells() parameters: Is there any difference in efficiency between passing in an integer or string?
Excel VBA - cells() parameters: Is there any difference in efficiency between passing in an integer or string?
hope this fix your issue If you've ever worked with cells in Excel VBA you've probably passed in integer values like this: , Pretty easy to test indeed, Tim.
TAG : excel
Date : January 11 2021, 03:34 PM , By : enginecrew
Vba - webscraping using ng-click
Vba - webscraping using ng-click
Hope this helps The desired element is an Angular element you need to induce WebDriverWait for the elementToBeClickable and you can use either of the following Locator Strategies:
TAG : excel
Date : January 11 2021, 03:32 PM , By : inquiringmind
How can I filter one column by name, then take value from another column?
How can I filter one column by name, then take value from another column?
this will help I am writing a script that will take a url and fetch its certificate information. I already have the certificate portion built out and need help with the excel piece. I have all the urls stored in a big excel file. , You can do the fol
TAG : excel
Date : January 11 2021, 03:32 PM , By : user96271
Send multiple Excel workbooks to relevant recipients
Send multiple Excel workbooks to relevant recipients
I hope this helps . To send email through VBA you can mainly use the Microsoft CDO for Windows Library or the Microsoft 16.0 Object Library (or earlier version). To use those libraries (after adding them) you can use different tutorials available onl
TAG : excel
Date : January 11 2021, 03:32 PM , By : Doug
Pivot table filtering based on a combo box
Pivot table filtering based on a combo box
it helps some times I find an alternative solutions that worked out at the end - thanks all for watching and supporting.
TAG : excel
Date : January 11 2021, 03:28 PM , By : ziqew
How do I concatenate multiple cells which fulfil a criteria in Sheets/Excel?
How do I concatenate multiple cells which fulfil a criteria in Sheets/Excel?
it should still fix some issue Assuming you actually meant B1:B4 in the return values, you could use the TEXTJOIN function depending on your Excel license. Follow the link to see if it would be available to you.
TAG : excel
Date : January 11 2021, 03:28 PM , By : Manik
Delete "unioned" non-contiguous range
Delete "unioned" non-contiguous range
With these it helps I can't use rng.EntireRow.Delete when the range is non-contiguous, and the range is built by Union. , You are trying to delete table rows like this:
TAG : excel
Date : January 11 2021, 03:26 PM , By : user107506
Insert subtotal for Excel spreadsheets
Insert subtotal for Excel spreadsheets
like below fixes the issue Can you add a column to each list that can help you categorize them? that way you can do a =SUMIF based on the categorized names. here's a code snipit and an image to reference:
TAG : excel
Date : January 11 2021, 03:26 PM , By : jch
Extending named range by 1 row
Extending named range by 1 row
With these it helps I think this is one of the solutions... but requires renaming named range.
TAG : excel
Date : January 11 2021, 03:26 PM , By : warttack
VBA Error while trying to delete rows that contain 0
VBA Error while trying to delete rows that contain 0
this will help I am creating a macro to delete every row where the value of its cell in column AN is 0. , I would use Range instead of Cells.
TAG : excel
Date : January 11 2021, 02:18 PM , By : Schmidt
How to make VBA read file path from a column?
How to make VBA read file path from a column?
I wish this helpful for you Assuming the corresponding cell is in the same row as the cell that was just edited, it would look like this:
TAG : excel
Date : January 11 2021, 02:18 PM , By : pjkinney
Dictionary compile error: method or data member not found
Dictionary compile error: method or data member not found
this one helps. Not sure why getting the above error. I have a class module named clsRemit with code: ,
TAG : excel
Date : January 11 2021, 02:18 PM , By : Simone
Count number of cells that match criteria with nested IF and COUNTIF statements
Count number of cells that match criteria with nested IF and COUNTIF statements
it should still fix some issue I'm trying to use an excel formula to count the number of cells in a given range (let's say T132:T134) that match a specific line of text, however cells should only be counted if in a different range (say F132:F134) the
TAG : excel
Date : January 11 2021, 11:39 AM , By : Amit Battan
excel + vba + compile error AutoOpenRequiredWorkbook (myFileNameToOpen, myFilePath) can't figure out why I'm getting thi
excel + vba + compile error AutoOpenRequiredWorkbook (myFileNameToOpen, myFilePath) can't figure out why I'm getting thi
To fix this issue I'm getting compile error of Expected:= when I run the following line of code: , Use the following:
TAG : excel
Date : January 11 2021, 11:39 AM , By : user184975
How do I link an Excel cell to a PowerPoint textbox so that the linked information uses the PowerPoint formatting but th
How do I link an Excel cell to a PowerPoint textbox so that the linked information uses the PowerPoint formatting but th
seems to work fine Probably the most time-efficient method is to format the Excel sheet to look like the result you want in PowerPoint. Excel links in PowerPoint are OLE objects, which visually reproduces the original Excel file.The alternative would
TAG : excel
Date : January 11 2021, 11:39 AM , By : Kirks
Compare two workbooks
Compare two workbooks
seems to work fine It's a little difficult to answer your question as it is right now because you didn't give any preliminary code or details of how you want the comparison to be done (via MsgBox, Debug.Print, generating a .xlsx or .txt file, etc.).L
TAG : excel
Date : January 11 2021, 11:39 AM , By : CM.
If else that checks 2 variables, but won't ignore text
If else that checks 2 variables, but won't ignore text
should help you out Since VBA will implicitly convert a text string that contains only numbers to a number for this comparison (i.e. 2>"1" is True, and 2>"3" is False), what you actually want to check if the value IsNumeric
TAG : excel
Date : January 10 2021, 02:06 PM , By : Chris Woods
What data type should a date be set to and why?
What data type should a date be set to and why?
it helps some times I was trying to assign a date to a variable. I tried: , This works:
TAG : excel
Date : January 10 2021, 02:06 PM , By : Juan Pablo
How to protect a specific workbook only in Excel VBA
How to protect a specific workbook only in Excel VBA
help you fix your problem Dean's answers in the comments: Early in the code (and in Worksheet_Change if appropriate) enter the following to define your sheet as an object (named default_ws in my case):
TAG : excel
Date : January 10 2021, 02:06 PM , By : mgaspar
(UserForm) ListBox Change event is not firing when ListBox List property is updated by code
(UserForm) ListBox Change event is not firing when ListBox List property is updated by code
With these it helps When this change to the ListBox happens, I want to Debug.Print "A message..." but with no success.
TAG : excel
Date : January 10 2021, 02:04 PM , By : jay
Powerquery running count by group
Powerquery running count by group
To fix the issue you can do I am creating a report where I do have two columns example of the table is below: , In PQ it's quite easy too:
TAG : excel
Date : January 10 2021, 02:04 PM , By : Gazza
Rank with condition
Rank with condition
I think the issue was by ths following , For such scenarios (ranking a subset of data), I find using SUMPRODUCT much easier:
TAG : excel
Date : January 10 2021, 02:04 PM , By : user105769
Excel VBA: importing CSV with dates as dd/mm/yyyy
Excel VBA: importing CSV with dates as dd/mm/yyyy
it fixes the issue I had the same problem with the .OpenText method. My Regional Settings are English - AU rather than NZ. I found another thread that suggested using .open, ( http://www.pcreview.co.uk/forums/date-format-error-csv-file-t3960006.html
TAG : excel
Date : January 10 2021, 01:49 PM , By : silvervino
VBA Public User Defined Function in Excel
VBA Public User Defined Function in Excel
around this issue The best place for functions such as this is in an Addin... To make an addin:Make a new workbook
TAG : excel
Date : January 10 2021, 01:45 PM , By : n3txpert
OleDB Jet - Float issues in reading excel data
OleDB Jet - Float issues in reading excel data
I hope this helps you . I just tested your data and this method posted here worked.i.e. the cell value kept it's precision 18.124962832, when put into DataSet.
TAG : excel
Date : January 10 2021, 01:45 PM , By : ussballantyne
Copying Hyperlink from Excel to Powerpoint in a template format
Copying Hyperlink from Excel to Powerpoint in a template format
like below fixes the issue It sounds like you only have 1 hyperlink, so the following should work. If there's more than 1, you would have to loop through the Hyperlinks collection to get the right one. In the top section where you gathering data from
TAG : excel
Date : January 09 2021, 02:14 PM , By : August
VBA - How to copy a column once but paste it twice?
VBA - How to copy a column once but paste it twice?
I wish did fix the issue. All you will need to do is paste twice. But it is important to note that you will need to update LastRow before pasting the second time. Also something worth mentioning, it is super recommended to use a worksheet object when
TAG : excel
Date : January 09 2021, 02:14 PM , By : Murali Ravipudi
Extracting from excel (.xlsx) writing to .txt file
Extracting from excel (.xlsx) writing to .txt file
I wish this helpful for you I'm assuming you want to make a txt file for each row? You need to close the file that has been written before doing another one.
TAG : excel
Date : January 09 2021, 02:14 PM , By : Guyou
Can InStr be used to find multiple String1 values?
Can InStr be used to find multiple String1 values?
hop of those help? You can try addition to shorten your code a bit and also perhaps make it more readable:
TAG : excel
Date : January 09 2021, 05:38 AM , By : TC.
Excel Macro - How to automatically copy a trendline from a graph to a cell
Excel Macro - How to automatically copy a trendline from a graph to a cell
will help you I want to automatically copy a trendline equation from a graph that is being regularly updated to a cell using macros. , You can use the following code to retrieve the trendline formula:
TAG : excel
Date : January 08 2021, 10:52 AM , By : Frank Bradley
Formula in Excel: Matching Year and Date
Formula in Excel: Matching Year and Date
it fixes the issue I have a date column and year column in this date column year must match with year column .. , Through conditional formatting: Select A2:B5
TAG : excel
Date : January 08 2021, 03:18 AM , By : nonkelhans
Remove Duplicates using vba
Remove Duplicates using vba
around this issue You could try creating a For loop accounting for A-Z as integers. I am not sure that this is the most efficient solution, but it could work for you. See below.
TAG : excel
Date : January 08 2021, 03:18 AM , By : TRobison
How to Write Select Query and Fetch Details to Excel |VBA|
How to Write Select Query and Fetch Details to Excel |VBA|
With these it helps This should make it work Taken from LinkSteps:
TAG : excel
Date : January 07 2021, 03:08 PM , By : Rob
bold cell based on specific value in column J
bold cell based on specific value in column J
will help you This answer is based on the assumption you looking for N/A errors on your worksheet caused by the same formulas in that column.
TAG : excel
Date : January 07 2021, 03:08 PM , By : Steve M
VBA - How to merge 2 columns while copying some data from the original row
VBA - How to merge 2 columns while copying some data from the original row
Does that help The error is caused by the fact that you copy the whole columns. Try this instead. It is not that much robust but it should work in this case:
TAG : excel
Date : January 07 2021, 03:08 PM , By : abuiles
Pull All Rows that Contain Same Invoice Number if Cell contains value
Pull All Rows that Contain Same Invoice Number if Cell contains value
seems to work fine This can be done with Power Query. Conceptually: Load the data into Power query, delete all columns except for the Invoice number and the column H. filter by the column H = True save as a connection Load the data into Power Query a
TAG : excel
Date : January 07 2021, 07:50 AM , By : WuJanJai
How can align text to the right using VBA, if a cell contains a specific value?
How can align text to the right using VBA, if a cell contains a specific value?
this one helps. I am trying to fix a spreadsheet, so that if the word TRAVEL is in a cell, then the contents of the cell is right justified.
TAG : excel
Date : January 06 2021, 03:17 PM , By : Killercode
ActiveCell recognition below 2nd row
ActiveCell recognition below 2nd row
may help you . I have a following code; splitting ActiveCell by"," and splitting the next column and looping it as a different rows. It works when ActiveCell is the first row but when it comes to the second row or below, it deletes next rows. , Try t
TAG : excel
Date : January 06 2021, 03:17 PM , By : user112141
How to convert FILETIME to Date in VBA?
How to convert FILETIME to Date in VBA?
I wish did fix the issue. I'm trying to write a module that will take a Hex FILETIME extracted from a registry key and parse that into a readable date in VBA. , Reveres first two bytes of the hex strings like this
TAG : excel
Date : January 06 2021, 03:27 AM , By : Liy
VLOOKUP can't return value more than once on a row
VLOOKUP can't return value more than once on a row
this one helps. may be you can try create new column on sheet1, and concatenate all the ROs. Quick way to concatenate the cells in range can be found in this link, https://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-ex
TAG : excel
Date : January 06 2021, 03:27 AM , By : user183275
How to Open & Close multiple excel workbooks based on a range of cells
How to Open & Close multiple excel workbooks based on a range of cells
Any of those help Minimal example:From row 3 to however many rows contain a value, open the workbook by setting it to wb, then close it with the optional parameter of True to save it.
TAG : excel
Date : January 05 2021, 11:29 AM , By : Chris Hanley
Excel Insists that my OpenXml file has errors
Excel Insists that my OpenXml file has errors
it helps some times Tracked this one down after some work too, and posting the answer here for posterity. Turns out Excel NEEDS you to put Fill Styles in the output file, even if you aren't using any fill styles in any of your cells.
TAG : excel
Date : January 05 2021, 11:29 AM , By : joshboles
How to deselect only one particular item in a slicer
How to deselect only one particular item in a slicer
Any of those help SOLVED Instead of trying to filter out the one particular product, I created a work-around. I filtered for only that product, copied the data onto the summary workbook, and then used a formula to subtract the singular product from t
TAG : excel
Date : January 05 2021, 11:29 AM , By : Anonymous
How to conditionally format cells which contain the same string of text?
How to conditionally format cells which contain the same string of text?
hope this fix your issue I am trying to change the colour of cells based on indivudual strings they contain rather than the entire value of the cell. Specifically, I want to format cells if they share the same string. , Use this formula in you Condit
TAG : excel
Date : January 05 2021, 11:29 AM , By : Anna
How to get the cell position/row of a search result
How to get the cell position/row of a search result
Hope that helps I am trying to compare a cell containing a long string (A1) with a list of keywords (C:C). I want to get the first matching cell position in order to reference the value in the corresponding array (D:D). , Use AGGREGATE to return the
TAG : excel
Date : January 05 2021, 11:29 AM , By : user116330
Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?
Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?
like below fixes the issue InStr returns an index. As a logical operator, And wants to have Boolean operands. Given Integer operands, the And operator is a bitwise operator - truth be told, these operators are always bitwise; we just dub them "logica
TAG : excel
Date : January 03 2021, 08:18 AM , By : Matt Watson
Data Validation based off multiple criteria
Data Validation based off multiple criteria
This might help you Unable to compile all formulas in single cell to validate the data , You would only use AND:
TAG : excel
Date : January 02 2021, 10:54 PM , By : Nandor Devai
How can I list horizontal data vertically without using transpose?
How can I list horizontal data vertically without using transpose?
may help you . If you have Office 365 Excel, one can use TEXTJOIN to create one line of text and parse that:
TAG : excel
Date : January 02 2021, 10:54 PM , By : Ronnie Carlin
Split full name into lastname,firstname if lastname has multiple parts (i.e. van, de)
Split full name into lastname,firstname if lastname has multiple parts (i.e. van, de)
will help you Split takes a third argument, "Limit", which can be used to stop splitting once the result will have that many entries.
TAG : excel
Date : January 02 2021, 06:48 AM , By : koder
can't get past block if
can't get past block if
wish of those help I am trying to format cells with interior color based on value of cell , You need to close the With and If statements:
TAG : excel
Date : January 02 2021, 06:48 AM , By : eferro
Data Validation in Userform to not allow / \ : * ? " < > | for textbox
Data Validation in Userform to not allow / \ : * ? " < > | for textbox
Any of those help TL;DR: You need a function that is able to check whether a given string contains any one of several forbidden characters. One way to do this is to define a string that contains each of these characters once, then iterate each charac
TAG : excel
Date : January 02 2021, 06:48 AM , By : James B
How to highlight duplicates in column that are not blanks?
How to highlight duplicates in column that are not blanks?
it helps some times I want to highlight all the duplicates of a concatenated string in column I and provide an error message if there are any duplicates highlighted. However, there are several blank cells in the column and I do not want these to show
TAG : excel
Date : January 02 2021, 06:48 AM , By : Val

shadow
Privacy Policy - Terms - Contact Us © scrbit.com