Help with Coding a Formula Please

[Paul Black]

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]

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

 

[Dana DeLouis]

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)

 

[Paul Black]

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]

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]

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]

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]

Hi Dave,

Splendid.
Thank you VERY Much for your Time, Effort & Help. It is Most
Appreciated.

All the Best.
Paul

 

[Dana DeLouis]

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

Hi Dave,

Splendid.
Thank you VERY Much for your Time, Effort & Help. It is Most
Appreciated.

All the Best.
Paul