logo
down
shadow

EXCEL QUESTIONS

VBA - Using criteria list select and clear or select an replace values
VBA - Using criteria list select and clear or select an replace values
wish helps you What makes you go for a VBA solution ? Hard to do much without a glance of you data and expected result.Non VBA option:
TAG : excel
Date : November 22 2020, 07:01 PM , By : Tom
vba Loop over a non-contiguous range
vba Loop over a non-contiguous range
I hope this helps . I have a non-contiguous range on rows (example address of myRange: $2:$2,$4:$205,$214:$214) and I would like to access a specific row and column within the range. I have tried the following: , Here are my entries - not necessarily
TAG : excel
Date : November 20 2020, 07:00 PM , By : jmkse
SSAS 2008/Excel 2007 - Can't see cube
SSAS 2008/Excel 2007 - Can't see cube
help you fix your problem The cube has a property Visible. If it's false, Excel won't list the cube.To see this property
TAG : excel
Date : November 19 2020, 07:01 PM , By : Faro
Output a NULL cell value in Excel
Output a NULL cell value in Excel
wish help you to fix your issue As you've indicated, you can't output NULL in an excel formula. I think this has to do with the fact that the formula itself causes the cell to not be able to be NULL. "" is the next best thing, but sometimes it's usef
TAG : excel
Date : November 18 2020, 07:00 PM , By : user3860963
Excel VBA Clear formats when value is 0
Excel VBA Clear formats when value is 0
this will help You have an XY problem. You're trying to improve the time it takes to repair your initial problem. Instead, you should just set the correct number format in the first place.
TAG : excel
Date : November 12 2020, 07:00 PM , By : P.Gruba
Value as hyperlink?
Value as hyperlink?
I hope this helps you . How to Create a Bookmark to the Same Worksheet A bookmark in Excel is similar to a hyperlink except that it is used to create a link to a specific area on the current worksheet or to a different worksheet within the same Excel
TAG : excel
Date : November 11 2020, 07:00 PM , By : Ivan Kuang
Adding references from VBA Causing Password Prompt To Appear
Adding references from VBA Causing Password Prompt To Appear
it helps some times You password protected your VBA project. Therefore, any changes to that project require it to be opened, so you get the password prompt. You can try and create an Add-In to work around this problem. It may work because you are cre
TAG : excel
Date : November 09 2020, 07:01 PM , By : haha
keep getting "Next without for" while I start with for
keep getting "Next without for" while I start with for
will be helpful for those in need I have a macro that runs on cell changes but every time I want to run it it keeps giving me "Next without for" or "double declaration within given range" Here's the code: , There is an End If missing right before Nex
TAG : excel
Date : November 05 2020, 07:01 PM , By : Phelps
How to color 56 cells in B column
How to color 56 cells in B column
this one helps. How to write a code that colors 56 cells in B column with 56 color from color index function-> cells.interior.colorindex , Use the following code:
TAG : excel
Date : November 04 2020, 07:01 PM , By : Saurav Anand
Excel from web scraping
Excel from web scraping
Any of those help Make sure you are selecting the right table. There are two table elements. The first is just headers. The second is headers + info. I am not sure you can use this method to get all the tabs though as the URL doesn't change and the c
TAG : excel
Date : November 03 2020, 07:01 PM , By : Geeta
Nested IF statements in Excel [Over the 7 allowed limit]
Nested IF statements in Excel [Over the 7 allowed limit]
hop of those help? You can do this much more elegantly with the VLOOKUP formula by making separate table mapping lower bounds to letters. The mapping table must be sorted by grade number ascending.For example:
TAG : excel
Date : November 03 2020, 07:01 PM , By : Jeevan
How can I copy data from one sheet to another sheet in Excel 2007 using a macro?
How can I copy data from one sheet to another sheet in Excel 2007 using a macro?
Any of those help The best way to learn how to do this is to record a macro. Excel has a very good macro recorder. Just start to record a new macro, then manually perform all the steps you describe in your question. After you have finished, stop the
TAG : excel
Date : November 01 2020, 07:01 PM , By : user3855482
Return a value of '1' a referenced cell is empty
Return a value of '1' a referenced cell is empty
will be helpful for those in need In Excel, I need to return a value of 1 if a referenced cell is empty , You can use:
TAG : excel
Date : November 01 2020, 07:01 PM , By : KevinM
How to create and update spreadsheet using OLEDB in C#?
How to create and update spreadsheet using OLEDB in C#?
wish of those help You need to add readonly= false to your connection string, see:Writing into excel file with OLEDB
TAG : excel
Date : October 31 2020, 08:00 PM , By : AdamLeon
Excel turning my numbers to floats
Excel turning my numbers to floats
With these it helps Excel isn't really messing up the field. Two things are happening: Excel formats large numbers in scientific notation. So "89234010000725515875" becomes "8.9234E+19" or "8.9234 x 10 ^ 19". The size of the number "89234010000725515
TAG : excel
Date : October 31 2020, 08:00 PM , By : Zhenfeng Liang
how to set new range variable to one cell address
how to set new range variable to one cell address
I wish this helpful for you You don't need the .address. All you need is this: Set newrng = Cells(x, 2)
TAG : excel
Date : October 30 2020, 08:01 PM , By : HogRing
Include new data entered each day in concatenate formula
Include new data entered each day in concatenate formula
it should still fix some issue =TEXT(B4,"m/d")" "C4" "TEXT(OFFSET(D4,0,COUNTA(D4:AH4)-1,1,1),"0.0%")I use the operator instead of CONCATENATE, but they do the same thing. The last element uses OFFSET. It starts at D4 and moves 0 rows down and x colu
TAG : excel
Date : October 29 2020, 08:01 PM , By : user3854491
SSRS export to Excel UNFORMATTED
SSRS export to Excel UNFORMATTED
around this issue You could export to a CSV and import that into a spreadsheet, possibly with some sort of template sheet for the particular report.
TAG : excel
Date : October 28 2020, 08:00 PM , By : lucianadleme
Is it possibule to insert 200 JPG image together in the excel file with the same size into its cell?
Is it possibule to insert 200 JPG image together in the excel file with the same size into its cell?
hop of those help? this will help you:http://en.allexperts.com/q/Excel-1059/2009/12/Excel-2007-VBA-insert.htm
TAG : excel
Date : October 28 2020, 08:00 PM , By : RajsSande
Lotus Notes Database Search
Lotus Notes Database Search
will be helpful for those in need In Notes there are two "parent"- classes to derive everything from. The NotesUIWorkspace is the class for the "frontend": It contains everything that you SEE in the client. The NotesSession is the class for the backe
TAG : excel
Date : October 28 2020, 04:55 PM , By : Ke Jiang
Extracting text from number of webpages list in Excel
Extracting text from number of webpages list in Excel
should help you out If all your links are in an Excel sheet, you could extract them with xlrd or openpyxl to have a Python list, i.e. ["http://link1", "http://link2"]Then you could use fetch the webpages using urllib.request to get the corresponding
TAG : excel
Date : October 25 2020, 07:10 PM , By : Ben Johnson
how to remove special characters from all the cells in sheet
how to remove special characters from all the cells in sheet
Does that help This is a very fast way to do it using Regular Expressions and a VBA Array. It uses early binding so you will need to add a VBA reference to "Microsoft VBScript Regular Expressions 5.5"
TAG : excel
Date : October 25 2020, 07:10 PM , By : Barbara Crooks
How can I clear the contents of (only) hidden cells in Excel (and not delete them)?
How can I clear the contents of (only) hidden cells in Excel (and not delete them)?
seems to work fine (context: I know a lot about writing complex formulas in Excel, but have very basic understanding of VBA. I am amazed that I couldn't find a solution to the problem I am trying to solve on my own which makes we wonder if there is s
TAG : excel
Date : October 25 2020, 07:10 AM , By : p.kuna
Using Application.OnKey to Disable CTRL-V
Using Application.OnKey to Disable CTRL-V
wish help you to fix your issue Only thing I can think of would be to perform a check on ActiveWorkbook.Name first:
TAG : excel
Date : October 24 2020, 06:10 PM , By : Mark Hunter
Not able to find unique values in excel
Not able to find unique values in excel
With these it helps So if this is your data:
TAG : excel
Date : October 22 2020, 06:10 PM , By : Sebastian Gonzalez V
Excel stealing keyboard focus from VCL Form (in AddIn)
Excel stealing keyboard focus from VCL Form (in AddIn)
hop of those help? I was having the same kind of problem. I am also implementing IDTExtensibility2 but as I am doing it on C++ I already managed to run the UI on a different thread. But anyway I was not fully happy with this solution. I would still h
TAG : excel
Date : October 22 2020, 06:10 PM , By : Martin Bussières
Multiplying an amount by 0.45 up until 833 then the remain by 0.25
Multiplying an amount by 0.45 up until 833 then the remain by 0.25
I hope this helps you . Fairly new to excel and its not something i would usually go into too much detail with, i used simple formula to get what little i need to do on it. , The formula should be:=MAX(A1-833,0)*0.25 + MIN(833,A1)*0.45
TAG : excel
Date : October 22 2020, 06:10 AM , By : Andy
Sort multiple rows Left-to-Right
Sort multiple rows Left-to-Right
fixed the issue. Will look into that further You need to sort each row on its own. Therefore loop row wise through the DataRange and sort each DataRow on its own.
TAG : excel
Date : October 22 2020, 06:10 AM , By : Keith Rockhold
Sorting data in columns with variable number of columns
Sorting data in columns with variable number of columns
fixed the issue. Will look into that further Loop through the columns of your sort range to add them to the sort order, like so:
TAG : excel
Date : October 21 2020, 06:10 PM , By : Pyrilix
Speeding up macro that goes through a workbook overwriting sheets by value
Speeding up macro that goes through a workbook overwriting sheets by value
fixed the issue. Will look into that further Ben, First thing would be to add a few controls to make sure Excel isn't allowed to calculated things:
TAG : excel
Date : October 21 2020, 06:10 PM , By : markboeder
How can I optimize this macro that sorts and formats SQL data?
How can I optimize this macro that sorts and formats SQL data?
I think the issue was by ths following , I'm working on an excel program that sorts and formats raw SQL data. I have a macro that takes awhile to run through the data, I would like to optimize it. , Here's how you'd implement @BigBen's suggestion:
TAG : excel
Date : October 21 2020, 06:10 PM , By : Henni Haj
Copy a cell to a merged cell while keeping hyperlink value
Copy a cell to a merged cell while keeping hyperlink value
I wish this help you Elaborating on the deconstruct I suggested... source cell has 2 parts, the content and link:
TAG : excel
Date : October 20 2020, 06:10 PM , By : Mukesh Dogra
Multiple IF arguments layered between cells
Multiple IF arguments layered between cells
Hope that helps I believe you can use a combination of IF() and COUNTIFS()If the answer in B2 is "no" then the score is set to zero, this can be achieved like so...
TAG : excel
Date : October 20 2020, 06:10 PM , By : Ahmed Khalid
432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
will help you there is a limit of up to three concurrent connections for Office365, as stated in this documentation:
TAG : excel
Date : October 20 2020, 06:10 AM , By : haisenbug
How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
fixed the issue. Will look into that further The problem is that Excel sees 09/03/2018@01:15:00.000 PM as a text string.To make it a date we need to remove the @ with SUBSTITUTE:
TAG : excel
Date : October 20 2020, 06:10 AM , By : Bang-Chieh Huang
Flashing animation getting interrupted by DoEvents
Flashing animation getting interrupted by DoEvents
will help you It's more of a workout than a solution, but it gets the job done, technically speakingIf you disable the userinput during the runtime of the flashbox procedure it will wait for the animation to finnish and only then re-enable it, you st
TAG : excel
Date : October 19 2020, 06:10 PM , By : Hari M
Delete multiple Excel Sheets in VBA
Delete multiple Excel Sheets in VBA
fixed the issue. Will look into that further I am using an excel Workbook for programtical generation. Once the workbook is created few of the sheets are having required data and few are blank with default templates only. , Try this:
TAG : excel
Date : October 18 2020, 06:10 PM , By : Yug Vohra
Delete text (left or right) with VBA so I only keep a specific string
Delete text (left or right) with VBA so I only keep a specific string
will be helpful for those in need Every day we get an Excel file which contains a database dump. Most of the file is being sorted in a VBA script that I made but I have one remaining problem that I'm unable to resolve. In one of the columns (A) I som
TAG : excel
Date : October 18 2020, 06:10 AM , By : Numismad
I Bad: VBA Confusion
I Bad: VBA Confusion
I hope this helps you . You need to reference in which worksheet your Cells() and Range() are. Otherwise, Excel cannot know that and starts guessing which worksheet you mean (and it fails guessing).So set a variable to a worksheet
TAG : excel
Date : October 18 2020, 06:10 AM , By : Nyan Htet
Add spaces to a string in excel vba, later used in .HTMLBody
Add spaces to a string in excel vba, later used in .HTMLBody
To fix this issue HTML collapses runs of multiple spaces to a single space - you need to use the non-breaking space entity nbsp; if you want a run of spaces.
TAG : excel
Date : October 17 2020, 06:10 PM , By : Omar Elsabban
Using While loop for getting the handle of rows in a particular Column
Using While loop for getting the handle of rows in a particular Column
it fixes the issue SpecialCells is your friend here.If you want all Cells that have Constant Values in them from Column A, then you can use Columns(1).SpecialCells(xlCellTypeConstants)
TAG : excel
Date : October 16 2020, 06:10 PM , By : SherWin
How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
Does that help So when your vlookup has an error you could put iferror() around the vlookup:
TAG : excel
Date : October 16 2020, 06:10 PM , By : mickell
FIND function in VBA - Object does not support this property or Method
FIND function in VBA - Object does not support this property or Method
Hope this helps You shouldn't use Like, that's to compare values. Find is a Range object that returns the range of the found value. Plus, you need to specify a range for Find to look in.For example:
TAG : excel
Date : October 15 2020, 06:10 AM , By : Aditiya Kurniawan
VBA Excel: “Automation Error. Exception occurred.” when using UserForm
VBA Excel: “Automation Error. Exception occurred.” when using UserForm
Any of those help I think I found the solution for this. For months opening a UserForm without first having the form open in VBA editor would tank the entire program.Another thread pointed out that Excel changed to loading forms in parallel, so when
TAG : excel
Date : October 14 2020, 04:00 AM , By : Mahesh Peddyakudi
Find specific lower or uppercase
Find specific lower or uppercase
hop of those help? , You can add UCase so that either "N" or "n" will be evaluaded as "N":
TAG : excel
Date : October 14 2020, 03:00 AM , By : Ajay Gite
group the results of a matching function
group the results of a matching function
With these it helps i have three columns in excel, one that holds the row id, the next one a product serial number, and the last one again some serial numbers. , Maybe in D1:
TAG : excel
Date : October 14 2020, 01:00 AM , By : Brewster Kahle
"Standard Deviation If" in VBA
"Standard Deviation If" in VBA
may help you . Here you go.This allows you to set the range outside of the formula, in case you want to use it elsewhere
TAG : excel
Date : October 14 2020, 01:00 AM , By : Sergey Shmakov
How to use Conditional Formatting?
How to use Conditional Formatting?
it helps some times How can I write a conditional formatting formula that will highlight the values in col. C is the value in B is a "Y" , The rule will be:
TAG : excel
Date : October 14 2020, 12:00 AM , By : manzo128
Match Function Shows Incorrect/Repeat Position
Match Function Shows Incorrect/Repeat Position
I hope this helps . The match function is not returning the correct position of the lookup value. , Posting comment as answer to this post can be listed as Answered.
TAG : excel
Date : October 13 2020, 08:00 PM , By : R. Torres
Add character to the beginning of a cell that start with number
Add character to the beginning of a cell that start with number
wish help you to fix your issue I need to add _ to the beginning of any cell that begins with a number. , Try using the following formula. Assuming the data were in cell A1:
TAG : excel
Date : October 13 2020, 08:00 PM , By : Ahmad Pratama
How can I handle DLL errors in VBA?
How can I handle DLL errors in VBA?
This might help you The API declaration: , From a comment:
TAG : excel
Date : October 13 2020, 06:00 PM , By : jin_0121
Copy a Hidden Excel Sheet
Copy a Hidden Excel Sheet
this one helps. The .Copy method creates a new workbook with only the copied sheet. At least one sheet must be visible in the any workbook (you can verify this independently by trying to create a new workbook with only 1 worksheet, and then try to Hi
TAG : excel
Date : October 13 2020, 11:00 AM , By : Dynazor
How to import a table from web page (with "div class") to excel?
How to import a table from web page (with "div class") to excel?
I wish this help you You don't need a browser to be opened. You can do this with XHR. The url I am using can be found in the network tab via F12 (Dev tools)If you search that tab after making your request you will find that url and the response has a
TAG : excel
Date : October 13 2020, 11:00 AM , By : Jahaber Sadhik
format excel file after export-csv powershell
format excel file after export-csv powershell
I think the issue was by ths following , There are some workarounds, but the easiest way would be to use dedicated module for this, such as ImportExcel by dfinke. It allows you to work with Excel files both ways (importing/exporting).Get-Help is your
TAG : excel
Date : October 13 2020, 11:00 AM , By : Dodders
Cancel = True is not working in Workbook_BeforeClose
Cancel = True is not working in Workbook_BeforeClose
wish helps you Change the name of error this is a reserved word for an already existing function (see Error function) and must not be used as a variable name. And activate Option Explicit and declare all your variables.Here is an example how you woul
TAG : excel
Date : October 13 2020, 11:00 AM , By : Huijun Zhang
Is there a way to retrieve information from a text file in vba?
Is there a way to retrieve information from a text file in vba?
I think the issue was by ths following , If you want to save your input file as an array you can first read the whole file and save it as one whole string. You can then use the Split function with the delimiter \n to return an array, where every elem
TAG : excel
Date : October 13 2020, 11:00 AM , By : ayodele samuel
Comparing two columns and if it matches then it should be highlighted in another column
Comparing two columns and if it matches then it should be highlighted in another column
Any of those help You can achieve this with Conditional Formatting.Create a New Rule and add in
TAG : excel
Date : October 13 2020, 11:00 AM , By : Himanki Saluja
How to get temperature of datetime value based on the table which contains day and night wise temperature data
How to get temperature of datetime value based on the table which contains day and night wise temperature data
this one helps. You can use MAXIFS to find the temperature where your specified datetime falls between the "from" and "To" values:
TAG : excel
Date : October 13 2020, 11:00 AM , By : Eslavath Vinod Kumar
How to Define a Pivot Cache?
How to Define a Pivot Cache?
help you fix your problem I am writing a code that will create a pivot table based on a dynamic range. I am having issues creating the pivot cache, however. When I run the code, no error message appears, but a blank worksheet is created with no pivot
TAG : excel
Date : October 13 2020, 08:00 AM , By : Daniel
Named argument not found vba compile error
Named argument not found vba compile error
wish help you to fix your issue According to the documentation of the Range.Sort method there do only exist 3 Key, Order and DataOption parameter not 14.
TAG : excel
Date : October 13 2020, 03:00 AM , By : gllrsu

shadow
Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk