Re: Formula to check if value is within range
oh, that is actually brilliant.. totally forgot about the ABS formula. thanks a lot Mike!
Re: Formula to check if value is within range
oh, that is actually brilliant.. totally forgot about the ABS formula. thanks a lot Mike!
Re: Formula to check if value is within range
Thanks for the input Kenneth - but what I am looking for is to check whether the result falls anywhere between -1 and 1. As in, if (A1+B1)=0.5, or 0.75, or 0.999, or -0.3 - this would all satisfy my criteria. I am essentially checking whether the total per two sheets in my workbook reconcile, but there's often a microscopic difference between the two due to rounding issues.
Also, just as I was posting this I've realized I should be able to play with the =ROUND() formula which will allow me to check if the rounded sum is equal to 0. So, thanks again for the help - in an unexpected way this pushed me towards the solution:)
Hi all, I'm wondering if there's a formula that could help me simplify the following calculation. I am checking whether the result of my calculation is within a specific range (between -1 and 1). To give you an example, here's how I'm checking whether -1<(A1+B1)<1:
Question: is there a way to simplify the
part? I.e., and I'm totally making this up, something along the lines of
.
My issue is that the A1+B1 part of the calc is actually a lot more complex, and I'd like to clean my formulas as clean as possible and not repeat the summation formula multiple times.
Thanks!
p.s. Would prefer to avoid using VBA/UDFs for this.
Re: error in running conversion macro
algaeginger, another general suggestion to make your code a lot clear - you actually never need to select anything in VBA (neither sheets nor ranges), it's an unnecessary step. For instance, your code can be written in the following manner:
This will copy cell A1 from the "Raw" sheet onto Sheet1 and paste it in cell A5.
Re: Cannot change number of decimals displayed
Found the solution myself. As I suspected, too many styles caused by data brought over from multiple workbooks into one file.
Solution: go to Home >> Number >> choose "More Number Formats.." from the drop-down >> on the "Number" tab select "Custom" >> you'll see a list of custom number formats that exist in your workbook. Scroll down past the preset ones that cannot be deleted, and delete one or multiple styles you do not use. This will free up the memory for new styles.
I'm working on a pretty big model in Excel and somewhere along the way I've lost the ability to increase or decrease the number of decimals displayed in the formatted cells. I've tried using Increase/Decrease Decimals buttons on the ribbon (Home >> Number). I also tried right-clicking on the cell, going to Format Cells, and increase the number of decimals on the Number tab there - after clicking ok, nothing happens, and when I go back to cell formatting it's set up to show the same number of decimals as before as if I haven't changed it.
Anyone run into a similar issue in the past? Any suggestions? I've tried running "XLStylesTool.exe" to remove excessive styles (thought that might be the problem), but that didn't seem to help.
Re: Run Macro After Opening a specific Workbook
Quote from BGFuel1086;758883That worked perfectly. So simple so easy. Thanks!
Glad it worked.
Hi,
I have some code that is executed if a shape is placed in a specific spot on the worksheet. I accomplish this by checking Shape.TopLeftCell property for each of the shapes on the worksheet.
The code works fine with "buttons" (rectangular shapes) I have on my sheet. However, apparently Excel treats a drop-down box I have on my sheet as a Shape object as well, and since apparently drop boxes don't support .TopLeftCell property my code errors out when it loops through the drop box Shape.
What I want to do: add an IF statement to ignore the drop box shape so the code don't test it. But I can't seem to find the proper language for this - I was hoping to put in something along the lines of
Any suggestions would be appreciated.
Re: VBA to Clear Row Contents Based on Multiple Criteria
What do you have as far as code goes? If you're not using a huge data set (tens of thousand of rows) you should be able to accomplish this using For Each loops. It will step through each cell and check criteria using If statements, e.g. If Range("A1").Value <5 Then Range("A1").ClearContents.
Re: Run Macro After Opening a specific Workbook
Are you saying your macro runs every time a new workbook is open, no matter if it's the workbook that contains the macro, or not?
If so, try testing if the name of the ActiveWorkbook (the one that's been just opened) is the same as the name of ThisWorkbook (the workbook that contains the macro.
I'm thinking something along the lines of
Re: Excel opens a blank workbook every time it's launched
Quote from rollis13;758729Sorry, no other idea :-(.
thanks for giving it a shot anyway!
Re: Excel opens a blank workbook every time it's launched
thanks rollis 13, unfortunately that didn't do the trick. I found two folders, both containing several files. I've tried both removing the files and changing their names - that didn't make a difference.
Re: Excel opens a blank workbook every time it's launched
Quote from rollis13;758714Which version of Excel are you using ?
Excel 2010 (32 bit), running Windows 7.
Hello all,
Every time I start Excel (whether just the application itself, or a specific workbook) it opens a blank workbook instead of just starting the application. Anyone had the same issue? I've tried a few things (playing with Advanced options etc) but nothing seemed to be directly related to the issue. Would appreciate any help.
UPD: the advanced options feature I was looking at is under File => Options => Advanced> General => At startup, open all files in...
Re: List of sheets referencing active sheet (dependent sheets)
wow, thanks a lot cytop! this is an awesome start:)
Hi all,
I recall seeing a macro that would show a messagebox with the names of all the sheets that are referencing (pulling data from) the current sheet. Does anybody have that macro and can share it? My search on google turned out nothing so far. Would appreciate your help.
Re: Deselecting range on an inactive sheet after copy-paste
thanks Kenneth, but the sheet is inactive and I cannot run a regular selection on it. I can activate the sheet and then activate another, but wanted to avoid all the hassle is possible.
Re: Deselecting range on an inactive sheet after copy-paste
Thanks Kenneth, tried that before, but it didn't do the trick for some reason.. Probably because I'm also inserting columns - are you aware of any code that might clear selection resulting from inserting columns? As in:
Hi all, looking for a more elegant way to do something really simple..
After copying and pasting a range on an inactive sheet that range remains selected after the code is done running and the user activates that sheet. Is there an easy way to select, say, cell A1 without activating the sheet affected by the copy-paste macro?