Re: Negative number return in a formula
Ah so - I see now Grasshopper!
Use the ABS function to ignore the -ve number:
=If(And(ABS($I$5)*(1-Margin)<ABS(F311),ABS(F311)<ABS($I$5)*(1+Margin)),1,"")
Regards
Weasel
Re: Negative number return in a formula
Ah so - I see now Grasshopper!
Use the ABS function to ignore the -ve number:
=If(And(ABS($I$5)*(1-Margin)<ABS(F311),ABS(F311)<ABS($I$5)*(1+Margin)),1,"")
Regards
Weasel
Re: Negative number return in a formula
Not really I'm afraid.
This is what your formula does:
The formula compares numbers to a base value ($I$5) with a tolerance of +/- 0.5%.
If the value lies within that margin the formula returns 1, otherwise it returns ""
Your sum formula then counts the number of values that meet the above conditions.
Did you need it to do something else?
Regards
Weasel
Re: Removing "Calculate" Prompt
Wilson,
I have move this thread to the main question forum - no idea why you are getting this bug.
Regards
Weasel
Re: Button to increment cell number
dj,
Place a button on the worksheet - makesure it is from the Controls toolbox not the Forms collection.
Double click the button to activate the code pane, in between the buttons click event (this should appear by default) place the following:
The end result looks like:
Private Sub CommandButton1_Click()
Sheets("sheet2").Range("a6").Value = Sheets("sheet2").Range("a6").Value + 1
End Sub
Regards
Weasel
Re: Negative number return in a formula
QuoteWhat if the formula (or another formula) would return 1 if it was not zero.
Not sure what you mean here.
=IF(A1<>0,1,"") ?
Regards
Weasel
Re: Displaying Sheet Names within one sheet
ethc,
There is no formula that refernces a sheet name. If you really want one paste this code into a standard module:
Private Function SheetName(Sheet_Index As Integer)
SheetName = Sheets(Sheet_Index).Name
End Function
Use it like this:
=sheetname(1)
where 1 is the order of the sheet in the workbook. Be warned if you change the order of the sheets in the book the column title will change but the references will still point to the original sheet.
Regards
Weasel
Re: Negative number return in a formula
dar168,
Welcome to Ozgrid!
Maybe I am missing your point, but yyour formula cannot possibly return a negative number.
The IF/AND formula will return 1 if TRUE and "" if FALSE.
Regards
Weasel
Re: Date range inability
kearnan,
There are two ways to approach this but your information was a little vague.
Method 1:
In this example I have assumed that the first period spans 17 Oct 2003 to 15 April 2004 - any date within these parameters returns 15 April 2004.
All other dates outside of this regardless of the year are retuned as 15 April 2004 +180 days (12 October 2004)
=IF(AND(L4>=DATEVALUE("10/17/03"),L4<=DATEVALUE("04/15/04")),DATEVALUE("04/15/04"),DATEVALUE("04/15/04")+180)
Method 2:
This uses a nested if to restrict the +180 to a definite date range, you will have to supply the final argument of what the formula should return if neither criteria is met.
=IF(AND(L4>=DATEVALUE("17/10/03"),L4<=DATEVALUE("04/15/04")),DATEVALUE("04/15/04"),IF(AND(L4>=DATEVALUE("04/16/04"),L4<=DATEVALUE("10/16/04")),DATEVALUE("04/15/04")+180,"Invalid Date")
Regards
Weasel
Re: Purdy Spreadsheet examples?
Amadeus,
Welcome in from the cold.
Making Excel look pretty is just a matter of playing around with the formatting tools however see here for some tips on good spreadsheet design.
I cannot emphasise enough that a good spreadsheet has its foundations in a clear and logical data layout, not how many fonts, colours and shades you can squeeze onto one page. Have a look at the AutoFormat under the Format menu - but unless your data is laid out in database table style this will unpredictable results.
Hope That Helps
Weasel
edit: I changed the name of the post to more accurately reflect the question. This assists with referencing the question with similar posts and the search function. Pleasae see the sticky note - http://http://www.ozgrid.com/forum/showthread.php?t=20947 for further details.
Re: make a formula change another cells value
No - a formula cant influence any cell other than the one it is entered into. Having said that, there is always a workaround but the success of this will depend on how intelligent you want the workaround to be.
Will the positive integer always be 1 digit?
Will it always be at the end of the text?
Regards
Weasel
Re: Autofill two rows using r1c1 notation.
Lera,
It is a little hard to suggest a more efficient way of performing the task without seeing the rest of the Sub, could you possible post the complete code (so long as it isnt 100s of lines )
Regards
Weasel
Re: Evaluating numbers less than x
Max,
see commented code below:
Sub Max0d()
Dim x As Integer
'declare scope of x
'base 0 means we will have to subtract 1 from J1 or we will get too many results
For x = 0 To Range("j1") - 1
'loop through the cells
'first cell will be D6 offset(0,0) which is still D6
If Range("d6").Offset(x, 0).Value < 0.07 Then
'when the If statement is true then the cell that is next to
'the cell currently being evaluated, offset(0,1) in the first case,
'will have the text failed placed into it.
Range("d6").Offset(x, 1).Formula = "Failed"
End If
Next x
End Sub
Display More
Regards
Weasel
Re: Evaluating numbers less than x
Max0d
There were a few logical errors in your code. Please see sample below and compare the differences, post back if you don't understand why it now works (hopefully ).
I presume that the cell J1 contains a count of the number of records.
Sub Max0d()
Dim x As Integer
For x = 0 To Range("j1") - 1
If Range("d6").Offset(x, 0).Value < 0.07 Then
Range("d6").Offset(x, 1).Formula = "Failed"
End If
Next x
End Sub
Display More
Regards
Weasel
Re: Evaluating numbers less than x
Max0d
Might have to post the rest of your code to see why it may be going wrong.
Regards
Weasel
Re: Setting Location of Programmatically added Controls by Cell
dopplex,
Welcome to Ozgrid - Glad you like it!
The example below uses the active cells left, top, width & height properties to add a command button to each cell in a selection of cells. To test highlight a range of cells and then run the macro.
Option Explicit
Sub ControlToCell()
Dim iLeft As Integer
Dim iTop As Integer
Dim iWidth As Integer
Dim iHeight As Integer
Dim cell As Range
For Each cell In Selection
cell.Select
iLeft = ActiveCell.Left
iTop = ActiveCell.Top
iWidth = ActiveCell.Width
iHeight = ActiveCell.Height
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=iLeft, Top:=iTop, Width:=iWidth, Height:= _
iHeight).Select
Next cell
End Sub
Display More
Hope that helps
Weasel
Re: Speeding up VBA Coding
Andrew,
Assuming:
1) Your table contains only the data you wish to effect
2) There are no gaps in the data
3) Row one had the column headers
The following should do as you wish -
Sub delHyphen()
Dim Cell As Range
'select all data in col G below header
Range(Range("g2"), Range("g65536").End(xlUp)).Select
For Each Cell In Selection
'if the value in the cell contains "-" then clear contents of entire row
If InStr(1, Cell.Value, "-") > 0 Then
Cell.EntireRow.ClearContents
End If
Next Cell
'sort be column g - this will place all cells with data at the top
Cells.Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes
End Sub
Display More
Regards
Weasel
Re: assign value to list from VBA
itsmaheshp,
You will have to provide a little more information here:
Does the cell contain a validation list? or
Do you want to add a value to the cell at the end of a list?
How do you propose to get the value that will be added:
User input?
Calculation?
Variable?
When is the value to be added:
Button click?
Conditions met?
Regards
Weasel
Re: formulas don't resolve
ymoriarty,
Welcome to OZGrid!
Could the formatting of cells be changed to text?
Try changing the format of the cell to General and then select the cell in Edit Mode (ie make the cursor flash in the formula bar), then hit enter.
Regards
Weasel
Re: Select In Select
Thanks Will,
I will try that out asap. My introduction to SQL so far has not covered the CASE keyword. Thats 6 keywords now - 994 to go
Weasel