AutoFill Help Please
Hello,
Could someone please help with the following Excel 2003 AutoFill problem? I am trying to create a macro so that when a new row of data is added to my worksheet, the formula in column J is automatically AutoFilled down to the last row of data. I only add one row of data at a time. So, I am basically only AutoFilling down one row at a time. The worksheet is named: SP155_Register The is a header row at row 2 The data rows start at row 3 The cell at J2 is called: STATUS If anyone can please help, it would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
AutoFill Help Please
right click on the worksheet tab and select view code. Paste in code like
this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lrowA As Long, lrowj As Long lrowA = Cells(Rows.Count, "A").End(xlUp).Row lrowj = Cells(Rows.Count, "J").End(xlUp).Row If lrowj < lrowA Then For i = lrowj + 1 To lrowA Cells(i, "J").FillDown Next End If End Sub -- Regards, Tom Ogilvy "Chris Hankin" wrote: Hello, Could someone please help with the following Excel 2003 AutoFill problem? I am trying to create a macro so that when a new row of data is added to my worksheet, the formula in column J is automatically AutoFilled down to the last row of data. I only add one row of data at a time. So, I am basically only AutoFilling down one row at a time. The worksheet is named: SP155_Register The is a header row at row 2 The data rows start at row 3 The cell at J2 is called: STATUS If anyone can please help, it would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
AutoFill Help Please
This is a tricky routine. Had to consider deleting rows as well as adding
row. The function doesn't do anythin if you add or delete row 3. Private Sub Worksheet_Change(ByVal Target As Range) Const StartCell = "J3" ' Prevent endless loops - changing cell causes ' routine to execute recursively ' Target address for insert row looks like $8:$8 If InStr(Target.Address, ":") < 0 Then LeftAddress = Left(Target.Address, InStr(Target.Address, ":") - 1) ' row 3 exit routine because we don't know what to do If (StrComp(Mid(LeftAddress, 2), "3") = 0) Then Exit Sub 'If column exit routine If (IsNumeric(Mid(LeftAddress, 2)) = False) Then Exit Sub RightAddress = Mid(Target.Address, InStr(Target.Address, ":") + 1) If StrComp(LeftAddress, RightAddress) = 0 Then RowCount = 0 Do While (1) Destination1 = Range(StartCell). _ Offset(rowoffset:=RowCount + 1, columnoffset:=0) Destination2 = Range(StartCell). _ Offset(rowoffset:=RowCount + 2, columnoffset:=0) 'thses statement are needed if rows are deleted MyError = IsError(Destination) Or IsError(Destination2) If MyError = False Then If (Destination1 = "") And (Destination2 = "") Then Exit Do End If End If Range(StartCell). _ Offset(rowoffset:=RowCount, columnoffset:=0).Copy _ Destination:=Range(StartCell).Cells. _ Offset(rowoffset:=RowCount + 1, columnoffset:=0) RowCount = RowCount + 1 Loop End If End If End Sub "Chris Hankin" wrote: Hello, Could someone please help with the following Excel 2003 AutoFill problem? I am trying to create a macro so that when a new row of data is added to my worksheet, the formula in column J is automatically AutoFilled down to the last row of data. I only add one row of data at a time. So, I am basically only AutoFilling down one row at a time. The worksheet is named: SP155_Register The is a header row at row 2 The data rows start at row 3 The cell at J2 is called: STATUS If anyone can please help, it would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
AutoFill Help Please
Thanks Tom and Joel,
Due to time differences in Australia, I will let you know how I went with your codes. Thanks again for all your efforts - very much appreciated. Cheers, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
AutoFill Help Please
Hello Tom and Joel,
Your codes worked very well thanks. Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
All times are GMT +1. The time now is 03:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com