Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula, Macro, or VBA code to find and insert | Excel Worksheet Functions | |||
Insert a specified number of rows dynamically | Excel Worksheet Functions | |||
Insert autoforms and objects dynamically | Excel Programming | |||
Code to Insert Formula in Cell | Excel Programming | |||
Dynamically insert a picture. | Excel Programming |