Insert formula with code dynamically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Desert Piranha - 25 Jan 2006 04:36 GMT

Hi all,
Ok don't laugh (to hard).
The goal is to insert a dynamic formula in Column 'I' Matching the last
used cell in Column 'C'.
By dynamic, i mean the formula should advance each cell downward.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim lLastrow As Long
With lLastrow = .Range("C65536").End(xlUp).Row
For Each rCell In .Range("I1:I" & lLastrow)
If rCell.Offset(6, 0).Value <> "" Then
rCell.FormulaR1C1 = "=IF(D2="","",(H2/G2))"
End If
Next rCell
End With
End Sub

Signature

Desert Piranha

Reply to this Message

Crowbar - 25 Jan 2006 14:57 GMT

Dim LastRow As Long
Dim RowNdx As Long
Dim OldVal As String

LastRow = Cells(Rows.Count, "I").End(xlUp).Row
OldVal = Range("C1")
For RowNdx = 5 To LastRow
If Cells(RowNdx, "I").Value = "" Then
Cells(RowNdx, "C").Value = OldVal
Else
OldVal = Cells(RowNdx, "C").Value
End If
Next RowNdx

Reply to this Message

Desert Piranha - 26 Jan 2006 00:34 GMT

Crowbar via OfficeKB.com Wrote:

> Dim LastRow As Long
> Dim RowNdx As Long
[quoted text clipped - 13 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200601/1Hi Crowbar,

I'v played with this for an hour or so. Haven't had any sucess yet,
will keep trying and post back.

Signature

Desert Piranha

Reply to this Message

Tom Ogilvy - 26 Jan 2006 05:04 GMT

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim lLastrow As Long
With lLastrow = .Range("C65536").End(xlUp).Row
Application.EnableEvents = False
For Each rCell In .Range("I1:I" & lLastrow)
If rCell.Offset(6, 0).Value <> "" Then
rCell.FormulaR1C1 = "=IF(D" & rCell.row & _
"="""","""",(H" & rCell.row & "/G" & rCell.row _
& "))"
End If
Next rCell
Application.EnableEvents = True
End With
End Sub

Signature

Regards,
Tom Ogilvy

> Crowbar via OfficeKB.com Wrote:
> > Dim LastRow As Long
[quoted text clipped - 17 lines]
> I'v played with this for an hour or so. Haven't had any sucess yet,
> will keep trying and post back.

Reply to this Message

Desert Piranha - 26 Jan 2006 05:56 GMT

Hi Tom,
Thx for replying.

With lLastrow = .Range("C65536").End(xlUp).Row
Error in this line "Range" gets highlighted in blue and says "Invalid
or unqualified reference"

FYI - Column 'C' has Text, Column 'G' and 'H' have numbers

Dave
Tom Ogilvy Wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rCell As Range
[quoted text clipped - 53 lines]
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=504764

Signature

Desert Piranha

Reply to this Message