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.
View 8 Replies
ADVERTISEMENT
Nov 29, 2006
This code only opens and closes a number of files found in a single folder.
Sub test2() ' opens and closes all files in a folder
Dim Mypath As Variant
Dim excelfile As Variant
Mypath = "W:A Construction2006FinancialPrivateInternal Monthly ReportsSeptember 2006"
excelfile = Dir(Mypath & "*.xls")
Application.DisplayAlerts = False
Do While excelfile <> ""
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.
View 9 Replies
View Related
Feb 4, 2014
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.
View 7 Replies
View Related
Dec 7, 2008
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.
View 5 Replies
View Related
Jun 11, 2009
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.
View 9 Replies
View Related
Jun 23, 2014
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.
View 1 Replies
View Related
Mar 27, 2007
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.
What is the best way to do this?
View 6 Replies
View Related
May 21, 2008
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?
View 8 Replies
View Related
Feb 1, 2013
How can we convert time on a scale of 100 to a scale of 60?
View 3 Replies
View Related
May 25, 2014
I am trying to turn a list into a correlation matrix.
Goes like this :
From :
1 1 1
1 2 0.6
1 3 0.7
2 2 1
2 3 0.9
3 3 1
To
1 2 3
1 1 0.5 0.6
2 e 1 0.9
3 e e 1
The first list is from worksheet 1, and im trying to create the matrix in worksheet 2. Both worksheet is in the same open workbook.
Here's the code I've been using
Sub button1_click()
Dim i As Integer
Dim j As Integer
Dim b As Integer
Dim a As Integer
Dim c As Integer
[Code] ........
Now the weird thing is, this worked on a smaller matrix before (31 x 31) but now it just would't work at all (85 x 85)
it keeps giving me the error run time 1004 : application defined or object defined error.
Here's the file : matrix.xlsx
View 1 Replies
View Related
Dec 10, 2008
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.
View 9 Replies
View Related
Jul 21, 2006
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.
View 7 Replies
View Related
Dec 13, 2013
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.
View 2 Replies
View Related
May 3, 2007
is there a way or a formula that turns numbers into their respective words:
ie Cell A1: 8500
Cell A2: Eight Thousand Five Hundred
Big ask I guess but its for a banking model so I can't be the first to have considered this
View 4 Replies
View Related
Sep 29, 2008
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.
View 2 Replies
View Related
May 22, 2014
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).
View 1 Replies
View Related
Apr 14, 2014
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.
View 1 Replies
View Related
May 5, 2014
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.
View 10 Replies
View Related
Nov 6, 2009
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?
View 5 Replies
View Related
Oct 21, 2013
I have a *.bmp image file. It is gray scale only. It amy be up to 1000 x 1000 pixels.
I want to extract the numerical value of each pixel and deposit the values into an Array for further processing.
View 1 Replies
View Related
May 29, 2014
Error Check Marco.xlsm
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?
View 8 Replies
View Related
Feb 7, 2007
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'
View 6 Replies
View Related
Dec 10, 2008
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.
View 4 Replies
View Related
Apr 12, 2007
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
View 9 Replies
View Related
Jun 18, 2014
A set low number of 100 is in cell B4, and a related set high percent of 18% is in cell B6.
A set high number of 1,000 is in cell D4, and a related set low percent of 14% is in cell D6.
In cell B8 a manual number will be typed between 100 and 1,000.
In cell B10 we want a formula that will roundup a percentage number between 14% and 18% depending on the number inserted in B8.
View 7 Replies
View Related
Jul 23, 2014
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.
View 1 Replies
View Related
May 9, 2014
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.
View 1 Replies
View Related
May 1, 2014
I would like to scale an image to 124% how do I do that
View 1 Replies
View Related
Feb 14, 2014
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?
View 5 Replies
View Related
Jan 26, 2009
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.
View 5 Replies
View Related
