Insert formula with code dynamically
 

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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=504764

Insert formula with code dynamically
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Insert formula with code dynamically
 

Crowbar via OfficeKB.com Wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ing/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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=504764

Insert formula with code dynamically
 

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

--
Regards,
Tom Ogilvy



"Desert Piranha"
wrote in
message news:Desert.Piranha.227uoc_1138235702.123@excelfor um-nospam.com...

Crowbar via OfficeKB.com Wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ing/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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=504764

Insert formula with code dynamically
 

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
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

--
Regards,
Tom Ogilvy



"Desert Piranha"
wrote in
message
news:Desert.Piranha.227uoc_1138235702.123@excelfor um-nospam.com...

Crowbar via OfficeKB.com Wrote:
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

--
Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...ing/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.


--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=504764



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=504764