View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Paul Black Paul Black is offline
external usenet poster
 
Posts: 394
Default 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