Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
Hi Everyone,
I would like a Macro that Inserts the Following Formula into an Excel Sheet in the Cells "C31:J95" Please. It will have the Format of "%" and be Two Decimal Places. I am Finding this Difficult Because of the Relative and Absolute References etc. I know that if I Insert this Formula into Cell "C31" and Copy Across and Down to Cell "J95" it Works. Here is the Formula :- =IF(ISERROR((COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30))," ",(COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30)) Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
You can fill a whole range of cells with formulas.
Select C31:J95, then with C31 the activecell, type out your formula. But instead of hitting enter, hit ctrl-enter. Excel will adjust the formula like filling across and down. In code, it would look like: Option Explicit Sub testme() Dim myFormula As String myFormula = "=IF(ISERROR((COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)" & _ "/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/" & _ "COMBIN($B$28,$B$27))^($B31-C$30)),"",(COMBIN($B31,C$30)*" & _ "(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*" & _ "(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30))" 'double up those pesky double quotes: " becomes "" myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) With ActiveSheet.Range("c31:j95") .Formula = myFormula .NumberFormat = "0.00%" End With End Sub Paul Black wrote: Hi Everyone, I would like a Macro that Inserts the Following Formula into an Excel Sheet in the Cells "C31:J95" Please. It will have the Format of "%" and be Two Decimal Places. I am Finding this Difficult Because of the Relative and Absolute References etc. I know that if I Insert this Formula into Cell "C31" and Copy Across and Down to Cell "J95" it Works. Here is the Formula :- =IF(ISERROR((COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30))," ",(COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30)) Thanks in Advance. All the Best. Paul -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
As a side note, I "think" this equation gives the same results. I may be
wrong though. =(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*COMBIN($B31,C$30) vs. ",(COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30) -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message oups.com... Hi Everyone, I would like a Macro that Inserts the Following Formula into an Excel Sheet in the Cells "C31:J95" Please. It will have the Format of "%" and be Two Decimal Places. I am Finding this Difficult Because of the Relative and Absolute References etc. I know that if I Insert this Formula into Cell "C31" and Copy Across and Down to Cell "J95" it Works. Here is the Formula :- =IF(ISERROR((COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30))," ",(COMBIN($B31,C$30)*(COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^C$30)*(1-COMBIN($B$28-1,$B$27-1)/COMBIN($B$28,$B$27))^($B31-C$30)) Thanks in Advance. All the Best. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
Dana DeLouis
Brilliant!, your Formula DOES Indeed give the Same Results. Dave Peterson Thanks VERY Much for the Reply & Code. I have Substituted Dana DeLouis' Formula for my Original One in your Code and it Works Great. One Other Question Please. I would like to Put a Number in Cell "B29" that will be the Number of Rows that I would like the Formulas to be Copied to. For Example, if I was to Enter the Value 100 in Cell "B29", I would like the Formulas to Fill Cells "C31:J130" and Also have the Values 1 to 100 ( like a Counter ) in Cells "B31:B130" Please. Is this Easy to do. Here is the Code I am Using :- Option Explicit Sub testme() Dim myFormula As String myFormula = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) With ActiveSheet.Range("c31:j95") .Formula = myFormula .NumberFormat = "0.00%" End With End Sub What does the ... myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) .... Bit Actually do Please. Thanks to you Both for your Time & Help. All the Best. Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
The easy question.
chr(34) is the double quote character ("). When you're creating a string in VBA, and you want to have that string include a " mark, then you have to double it. Dim myStr As String myStr = "asdf""asdf" Debug.Print myStr You'll see: asdf"asdf Because I get lost in long strings doubling up the double quotes, I used application.substitute to do the work for me. (If you're running xl2k+, you could use Replace() instead.) And because I get confused even with: myFormula = Application.Substitute(myFormula, """", """""") I like to use chr(34). Option Explicit Sub testme() Dim myFormula As String Dim FirstCell As Range Dim FirstRow As Long Dim TotalRows As Variant myFormula _ = "=(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*COMBIN($B31,C$30)" myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) With ActiveSheet Set FirstCell = .Range("c31") FirstRow = FirstCell.Row TotalRows = .Range("b29").Value If Application.IsNumber(TotalRows) = False Then MsgBox "Put a number in B29!" Exit Sub End If With FirstCell.Resize(TotalRows, 8) 'C:J is 8 columns. .Formula = myFormula .NumberFormat = "0.00%" End With With FirstCell.Offset(0, -1).Resize(TotalRows, 1) .Formula = "=row()-" & FirstRow - 1 .Value = .Value End With End With End Sub ..offset(0,-1) means to stay in the same row (0) and go one column right (-1). ..resize(totalrows,1) means make it whatever number of rows by 1 column. And with Dana's formula improvment, you don't actually need that .substitute line--but if it doesn't hurt too much--just in case you put "" in your formula. Paul Black wrote: Dana DeLouis Brilliant!, your Formula DOES Indeed give the Same Results. Dave Peterson Thanks VERY Much for the Reply & Code. I have Substituted Dana DeLouis' Formula for my Original One in your Code and it Works Great. One Other Question Please. I would like to Put a Number in Cell "B29" that will be the Number of Rows that I would like the Formulas to be Copied to. For Example, if I was to Enter the Value 100 in Cell "B29", I would like the Formulas to Fill Cells "C31:J130" and Also have the Values 1 to 100 ( like a Counter ) in Cells "B31:B130" Please. Is this Easy to do. Here is the Code I am Using :- Option Explicit Sub testme() Dim myFormula As String myFormula = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) With ActiveSheet.Range("c31:j95") .Formula = myFormula .NumberFormat = "0.00%" End With End Sub What does the ... myFormula = Application.Substitute(myFormula, Chr(34), Chr(34) & Chr(34)) ... Bit Actually do Please. Thanks to you Both for your Time & Help. All the Best. Paul -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
Hi Dave,
Thanks VERY Much for the Update and Detailed Description. One Final Request Please. Because the Value in Cell "B29" can be Less Or More Each Time it is Run, I would like it to Delete the Rows that were Used Previously Before Producing the New Data. I have Tried the Following But Without Any Success. Range("B31", Range("J:").End(xlDown).Offset(-1, 0)).EntireRow.Delete Range("B31", Range("J:").End(xlDown)).EntireRow.Delete Range("B31:J").Select Selection.Delete Shift:=xlUp Range("B31:J").End(xlDown).Select Selection.Delete Shift:=xlUp Here is the Code I am Currently Using :- Option Explicit Sub testme() Dim myFormula As String Dim FirstCell As Range Dim FirstRow As Long Dim TotalRows As Variant myFormula _ = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"""",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" With ActiveSheet Set FirstCell = .Range("c31") FirstRow = FirstCell.Row TotalRows = .Range("b29").Value If Application.IsNumber(TotalRows) = False Then MsgBox "Put a number in B29!" Exit Sub End If With FirstCell.Resize(TotalRows, 8) 'C:J is 8 columns. .Formula = myFormula .NumberFormat = "0.00%" End With With FirstCell.Offset(0, -1).Resize(TotalRows, 1) .Formula = "=row()-" & FirstRow - 1 .Value = .Value End With End With End Sub Most Appreciated. Thanks in Advance. All the Best. Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
It sounds like you can delete all the rows in 31:65536???
If yes, then (within the with/end with structure): .rows("31:65536").clear or .rows("31:65536").clearcontents or .rows("31:65536").delete (maybe???) Paul Black wrote: Hi Dave, Thanks VERY Much for the Update and Detailed Description. One Final Request Please. Because the Value in Cell "B29" can be Less Or More Each Time it is Run, I would like it to Delete the Rows that were Used Previously Before Producing the New Data. I have Tried the Following But Without Any Success. Range("B31", Range("J:").End(xlDown).Offset(-1, 0)).EntireRow.Delete Range("B31", Range("J:").End(xlDown)).EntireRow.Delete Range("B31:J").Select Selection.Delete Shift:=xlUp Range("B31:J").End(xlDown).Select Selection.Delete Shift:=xlUp Here is the Code I am Currently Using :- Option Explicit Sub testme() Dim myFormula As String Dim FirstCell As Range Dim FirstRow As Long Dim TotalRows As Variant myFormula _ = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"""",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" With ActiveSheet Set FirstCell = .Range("c31") FirstRow = FirstCell.Row TotalRows = .Range("b29").Value If Application.IsNumber(TotalRows) = False Then MsgBox "Put a number in B29!" Exit Sub End If With FirstCell.Resize(TotalRows, 8) 'C:J is 8 columns. .Formula = myFormula .NumberFormat = "0.00%" End With With FirstCell.Offset(0, -1).Resize(TotalRows, 1) .Formula = "=row()-" & FirstRow - 1 .Value = .Value End With End With End Sub Most Appreciated. Thanks in Advance. All the Best. Paul -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
Hi Dave,
Splendid. Thank you VERY Much for your Time, Effort & Help. It is Most Appreciated. All the Best. Paul Dave Peterson wrote: It sounds like you can delete all the rows in 31:65536??? If yes, then (within the with/end with structure): .rows("31:65536").clear or .rows("31:65536").clearcontents or .rows("31:65536").delete (maybe???) Paul Black wrote: Hi Dave, Thanks VERY Much for the Update and Detailed Description. One Final Request Please. Because the Value in Cell "B29" can be Less Or More Each Time it is Run, I would like it to Delete the Rows that were Used Previously Before Producing the New Data. I have Tried the Following But Without Any Success. Range("B31", Range("J:").End(xlDown).Offset(-1, 0)).EntireRow.Delete Range("B31", Range("J:").End(xlDown)).EntireRow.Delete Range("B31:J").Select Selection.Delete Shift:=xlUp Range("B31:J").End(xlDown).Select Selection.Delete Shift:=xlUp Here is the Code I am Currently Using :- Option Explicit Sub testme() Dim myFormula As String Dim FirstCell As Range Dim FirstRow As Long Dim TotalRows As Variant myFormula _ = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"""",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" With ActiveSheet Set FirstCell = .Range("c31") FirstRow = FirstCell.Row TotalRows = .Range("b29").Value If Application.IsNumber(TotalRows) = False Then MsgBox "Put a number in B29!" Exit Sub End If With FirstCell.Resize(TotalRows, 8) 'C:J is 8 columns. .Formula = myFormula .NumberFormat = "0.00%" End With With FirstCell.Offset(0, -1).Resize(TotalRows, 1) .Formula = "=row()-" & FirstRow - 1 .Value = .Value End With End With End Sub Most Appreciated. Thanks in Advance. All the Best. Paul -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Coding a Formula Please
Hi. Glad it's working. Just throwing out an idea here. Earlier you mentioned that... I am Finding this Difficult Because of the Relative and Absolute References etc. Would working with Range Names help? Also, it looks to me that the only errors would be if B28 were 0 (divide by 0) or an invalid Combin( ). As an idea, perhaps only test for these two. Again, just throwing out some ideas... :) Sub TestMe() Dim TotalRows As Long Const Fx As String = _ "=IF(ISERROR((1/Y)+COMBIN(m,n)),"""",(1-X/Y)^(m-n)*(X/Y)^n*COMBIN(m,n))" TotalRows = [B29] If Not WorksheetFunction.IsNumber(TotalRows) Then MsgBox "Put a number in B29!" Exit Sub End If ActiveWorkbook.Names.Add "X", [B27] ActiveWorkbook.Names.Add "Y", [B28] ActiveWorkbook.Names.Add "n", [C30:J30] ActiveWorkbook.Names.Add "m", [B31].Resize(TotalRows) Rows("31:65536").ClearContents With [B31] .Value = 1 .AutoFill .Resize(TotalRows), xlFillSeries End With With [C31].Resize(TotalRows, 8) .Formula = Fx .NumberFormat = "0.00%" End With End Sub -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message oups.com... Hi Dave, Splendid. Thank you VERY Much for your Time, Effort & Help. It is Most Appreciated. All the Best. Paul Dave Peterson wrote: It sounds like you can delete all the rows in 31:65536??? If yes, then (within the with/end with structure): .rows("31:65536").clear or .rows("31:65536").clearcontents or .rows("31:65536").delete (maybe???) Paul Black wrote: Hi Dave, Thanks VERY Much for the Update and Detailed Description. One Final Request Please. Because the Value in Cell "B29" can be Less Or More Each Time it is Run, I would like it to Delete the Rows that were Used Previously Before Producing the New Data. I have Tried the Following But Without Any Success. Range("B31", Range("J:").End(xlDown).Offset(-1, 0)).EntireRow.Delete Range("B31", Range("J:").End(xlDown)).EntireRow.Delete Range("B31:J").Select Selection.Delete Shift:=xlUp Range("B31:J").End(xlDown).Select Selection.Delete Shift:=xlUp Here is the Code I am Currently Using :- Option Explicit Sub testme() Dim myFormula As String Dim FirstCell As Range Dim FirstRow As Long Dim TotalRows As Variant myFormula _ = "=IF(ISERROR((1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30)),"""",(1-$B$27/$B$28)^($B31-C$30)*($B$27/$B$28)^C$30*" & _ "COMBIN($B31,C$30))" With ActiveSheet Set FirstCell = .Range("c31") FirstRow = FirstCell.Row TotalRows = .Range("b29").Value If Application.IsNumber(TotalRows) = False Then MsgBox "Put a number in B29!" Exit Sub End If With FirstCell.Resize(TotalRows, 8) 'C:J is 8 columns. .Formula = myFormula .NumberFormat = "0.00%" End With With FirstCell.Offset(0, -1).Resize(TotalRows, 1) .Formula = "=row()-" & FirstRow - 1 .Value = .Value End With End With End Sub Most Appreciated. Thanks in Advance. All the Best. Paul -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting question; function, formula, or coding? | Excel Worksheet Functions | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Formula Color Coding | Excel Discussion (Misc queries) | |||
vba coding for formula in cell | Excel Programming | |||
Coding help | Excel Programming |