Turn Off Error Alert When Using Zero Values In A Logarithmic Scale
May 26, 2006
I'm creating several dozen charts all using Logarithmic Scale and while I know you aren't supposed to provide Zero values in the source data this is one project where that can't be helped. My problem is that the Excel Alert keeps popping up to tell me I shouldn't use zeros and it keeps crashing my sheet because it pops up every 10 seconds or so.
My question is: Is there any way to turn off an Excel Error Alert? OR Is there any value I can place in those cells (other than Zero or One) that would stop that troublesome alert from popping up? Just for reference, this is the alert: MICROSOFT EXCEL
Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following: * Enter only positive values (greater than zero) int he cells used to create the chart. * In the chart, click the axis you want to change. On the Format menu, click Selected Axis. Click the Scale tab, and then clear the Logarithmic Scale check box.
Set wbOpen = Workbooks.Open(Filename:=Mypath & excelfile) excelfile = Dir MsgBox excelfile wbOpen.Close
Loop
Application.DisplayAlerts = True
End Sub
I am getting Update/Don't Update Links from those files requiring same that I wish not to receive even though I am using Application.Display. Alerts code above.
log linear Regression. The thing is I do not know how to calculate elasticity of logarithmic values, slope, std. error in order to make forecasts. Is there a way solve it with Linest or any other formula. I am attaching a spreadsheet to better see.
I just discovered VBA coding today and was wondering if I could use it as an audio alert. I found a couple examples online but dont understand the coding language of excel. I have a feed link from my trading platform to excel that feeds in live data. What I would like to do is have an alert for when Range ("C4").select is >=1000 Alert Ring.wav and when Range ("C4").select is <=-1000 Alert Chimes.wav.
I wanted to make a pop up alert message appear whenever the formula in a cell gives a particular output.
Before posting this i tried looking for similar posts before, but the ones found do not solve my problem. There it asks me to write the following code in VBA,
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As String
MyRange = "A1"
If Me.Range(MyRange).Value = "Have Meeting" Then MsgBox "Have Meeting" End If End Sub
But my doing so, I can not use any formula in the cell A1 or any other cell i give reference as MyRange. I will always have to physically type "Have Meeting" or any other word assigned in the above.
I'm using Data Validation for various fields in a form and I'm trying to make it user friendly by adding Error Alert conditions and text explaining why the error has occurred. I also want the form to be translatable into other languages.
For some VBA message boxes that get displayed I can put the English text in some hidden cells and that can be translated easily by someone without access to the code. But I cannot figure out how to make the Data Validation Error Alert messages point to a cell so that they are also translatable without forcing the translator to open each Data Validation dialog.
Each month I get a report that I process using a macro. The problem is that each month the name of the file changes and is different. When I run the macro and it crashes I have to de-bug by going through the code to change all references to the file name from the the previos run and change to the latest file name and then re-run the macro. There must be a better way.
I would like to learn the code that sees the open book and then refers to it for the run.
I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.
I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?
I am using Jon Peltier's worksheet_change event code to try and automate chart axes.
Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$AG$5" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MaximumScale = Target.Value Case "$B$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MinimumScale = Target.Value Case "$AG$7" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _ .MajorUnit = Target.Value Case "$L$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MaximumScale = Target.Value Case "$N$3" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MinimumScale = Target.Value Case "$AH$7" ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _ .MajorUnit = Target.Value Case Else End Select End Sub
However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
I have a user that wants me to calculate a value for x. The user will input the variables a and b into this equation: b/a = .0231x * EXP(.0439*x). The user insists that the solution to this problem must be arrived iteratively, honing in on the correct answer. Is that right? It looks to me that surely there is a way to solve this mathematically.
Every so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.
For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula
=10/2, the only value inside is 5.
How do I keep this happening ??? I want the cells to keep the formulas.
I am trying to create a s/sheet whereby one can calculate how the concentration of an isotope changes (via an exponential decay) with respect to 2 variables:
1. its half-life (arbitrarily shown as 3 'units' in cell E2), and 2. its initial concentration or dose (arbitrarily shown as 100 units) AND subsequent additional exposures (arbitrarily shown as exposure to 50 units every 3 days) (shown in column E).
Thus for example, with an initial dose of 100 units (cell E6), after 3 days, the substance would have decayed by one half-life to 50 units. However, because there was an ADDITIONAL exposure of 50 concentration units on day 3, the EFFECTIVE dose on day 3 = 100 units.
I have 1 column of pasted values that basically look like this c 1:a 2:b 3:c 1:d 2:e 3:f etc...
And they need to look like this: c1 c2 c3 a b c d e f
I know how to separate the first column into two columns by separating the values by the colon. But how do I rearrange the values as above and combine all "1"s in c1 so it becomes the headers and the column is populated only with the values? The table is too big to do manually (thousands of rows).
I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.
ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....
The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.
I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).
The problem is that if there is no data in a participants column then excel correctly gives you readout of #DIV//0!. On my results page this #DIV//0! makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0! to 0 or even turn it to a blank cell?
see attached example. I am trying to write an error detection routine that iterates through worksheets that have numeric values for names (ignore text names or alphanumeric). Macro checks range on each numeric worksheet E3:E33 and is supposed to report back on the SummarySheet if any value other than 1 or 0 is found in range E3:E33 on any numeric-name worksheet. Code as follows:
[Code] ....
Problem is that it just reports EVERY worksheet as having an error when clearly most don't (none do I think in the attached example).
Try changing some of ranges E3:E33 to values other than 1 or 0, it still reports all sheets. Why the macro does not evaluate the range E3:E33 properly and just reports every worksheet as having an error?
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6() Cells.Select selection.Copy selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
For which I get 'Compile Error - Expected Function or Variable'
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.
The scale on our chart in the X axis is mandating a numeric number. It is showing "39200" instead of 4/28/07. In any event, we would like to have 4/9/07 -- the last date we have data for and the last date that we have referenced on the X axis.
The chart is in the attached chart.xls and the data is in the attached chart.xls
I am trying to create a scale using IF functions in Excel.
I have a row of numbers that I want to convert to my scale. My scale is fairly simple, less than 1 =1 and more than 10 = 1 After that, any number between 1-2 gets a 10, any number between 2-3 gets a 9, any number between 3-4 gets a 8, etc.
So, what I have done is take a logic if-then function and applied it to my row of numbers. It looks something like this:
=IF(2<D2<3,9)
However, this doesn't work. I have a number (2.55), and instead of showing a 9, it says FALSE. I am pretty sure 2.55 falls between 2 and 3 on the scale. Why won't it work?
Also, is there any way to create a function where I could put each of my if/then functions to create my whole scale? Right now I just have 10 separate columns for each number on my scale.
Here's the scenario. A co-worker and I access the same file from the same online database and download it in the same manner. We have the same version of excel. We then open these identical files, we run a formatting macro (identical), but when it comes time to set the page breaks in order to get the items to print on the same page I have to increase a columns width to make it fit on one page and yet it still doesn't match theirs. It appears to have a different scale but I'm not sure of the option difference between our machines. I don't know how it could be the printer since it physically gives her different page break option than what mine does.
My display scale is 125%. A college's is set to %100. This causes errors when I place Shapes to saved locations, .Top & .Left programmatically. Based on Google searches, it appears that setting the auto scale mode to DPI may solve this problem. How do I set it?
I have a list of items that each one has starting price, demands and current price. Current price changes by demands according to pre determined scale. Demands values are entered manually by user. Im looking for a formula that will calculate current price automatically according to the scale & demands. I cannot know what will be the amount of demands (it could be 0 or 5000...) but I do know that demands are sequential integer numbers, positive or negative. I also dont want it to exceed the minimum and maximum value that determine by user. I think the attached file will explain well than what Im writing.
