Autofill below row based above row ...

[maabadi]

Hi
I want a macro to when I change or add value in one cell (e.g: D5) then auto-fill the below row ( row no. 6 from column A to M) and convert the formula in above row (row no. 4 from column A to M) convert to number to decrease size of file.
I recorded this macro in excel but it only work for one row.
Please Help me and correct it.

Sub Macro2()
'
' Macro2 Macro
'

'
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "45"
    ActiveCell.Offset(0, -4).Range("A1:M1").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:M2"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:M2").Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Offset(-1, 0).Range("A1:M1").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Joe4]

I want a macro to when I change or add value in one cell (e.g: D5) then auto-fill the below row ( row no. 6 from column A to M)

What column can we use to determine how far down this formula should be AutoFill (i.e. which column always has data in it)?

convert the formula in above row (row no. 4 from column A to M) convert to number to decrease size of file.

Note sure what you mean by this.

It may be helpful if you post an actual example of your data and walk us through what the end result should look like.

 

[maabadi]

Hi joe4
1. I want whenever change or add value on column D only One row each time auto-fill (because I have formula on column A to M).
2. I want to convert row above changed cell to decrease file because I have more than 7 sheets and in 2 sheets I have more than 10000 row data from column A to S. I want when I add value in Column D , formula add value to another cell in same row and the same time the above row formula convert to number and below row auto-fill by this Row (from column A to M) formula.

If both of them isn't possible at the same time, I want only autofill below row. Also I want a worksheet change event by I am dummy and don't know how describe range and cell for it.

Thanks.
Maabadi

 

[Joe4]

It still isn't really clear (I am guessing some of it may be language barrier, certain phrases like "decrease file" don't really make sense). I think a snapshot of your data and your expected results would be really be helpful and probably go a long way in showing us what you want.

There are tools you can use to post screen images mentioned in Section B of this post here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
There is also a "Test Here" forum you can use to test these tools before you try to post the images in this thread here.

 

[maabadi]

Hi Joe4.
this is my table From row 1 to 5 in "EnterExit" Sheet.
[TABLE="class: grid, width: 1500, align: center"]
<tbody>[TR]
[TD]Seed Code[/TD]
[TD]No.[/TD]
[TD]Seed Code & No.[/TD]
[TD]Seed Name[/TD]
[TD]Date[/TD]
[TD]WeekDay[/TD]
[TD]Weight of Enter(Kg.)[/TD]
[TD]From[/TD]
[TD]Production Place[/TD]
[TD]Production Year[/TD]
[TD]Weight of Exit (Kg.)[/TD]
[TD]To[/TD]
[TD]Total Remaining Weight (Kg.)[/TD]
[/TR]
[TR]
[TD]1101[/TD]
[TD]1[/TD]
[TD]1101001[/TD]
[TD]401 su ir Hybrid[/TD]
[TD]2014/09/24[/TD]
[TD]Sunday[/TD]
[TD]0.5[/TD]
[TD]25[/TD]
[TD]Breeding[/TD]
[TD]2014[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]4006[/TD]
[TD]1[/TD]
[TD]4006001[/TD]
[TD]B-88-Bs-25[/TD]
[TD]2015/10/16[/TD]
[TD]Thursday[/TD]
[TD]1.365[/TD]
[TD]F3[/TD]
[TD]ISO. 8[/TD]
[TD]2015[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1.365[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]1[/TD]
[TD]1111001[/TD]
[TD]A-688w[/TD]
[TD]2014/09/27[/TD]
[TD]Wednesday[/TD]
[TD]2.89[/TD]
[TD]66[/TD]
[TD]Breeding[/TD]
[TD]2014[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2.89[/TD]
[/TR]
[TR]
[TD]4006[/TD]
[TD]2[/TD]
[TD]4006002[/TD]
[TD]B-88-Bs-25[/TD]
[TD]2016/03/22[/TD]
[TD]Saturday[/TD]
[TD]0[/TD]
[TD]F3[/TD]
[TD]ISO. 8[/TD]
[TD]2015[/TD]
[TD]0.445[/TD]
[TD]Planting[/TD]
[TD]0.92[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]2[/TD]
[TD]1111002[/TD]
[TD]A-688w[/TD]
[TD]2016/03/25[/TD]
[TD]Monday[/TD]
[TD]0[/TD]
[TD]66[/TD]
[TD]Breeding[/TD]
[TD]2014[/TD]
[TD]1.26[/TD]
[TD]Dezful[/TD]
[TD]1.73[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have more than 4000 Seed type in another sheet and have formula in another cell in same row ( column B,C,D,F and M). I want when I add data in column A or E formula added to below row, Because If I define Formula for 60000 row from Column A to M, I have very big file size and it coming up very late When I Open it. Then I want to convert formula in previous rows to absolute number and fill down one row below formula from this row.
Thanks

 

[Joe4]

I think I see what you are after now. If we use a Worksheet_Change event, we can make this code run automatically once both columns A and E are updated in a row.

To use this code, right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste the code into the resulting VB Editor window in the appropriate Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRow As Long

'   Check to make sure only one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Check to see if cell just updated in columns A or E
    If Target.Column = 1 Or Target.Column = 5 Then
        myRow = Target.Row
'       Check to make sure both columns A and E are populated
        If Cells(myRow, "A") <> "" And Cells(myRow, "E") > 0 Then
'           Copy formulas from columns B, C, D, F, and M down
            Application.EnableEvents = False
            Range("B" & myRow - 1 & ":D" & myRow - 1).AutoFill Destination:=Range("B" & myRow - 1 & ":D" & myRow), Type:=xlFillDefault
            Range("F" & myRow - 1).AutoFill Destination:=Range("F" & myRow - 1 & ":F" & myRow), Type:=xlFillDefault
            Range("M" & myRow - 1).AutoFill Destination:=Range("M" & myRow - 1 & ":M" & myRow), Type:=xlFillDefault
'           Hard-code formulas in previous row
            Rows(myRow - 1) = Rows(myRow - 1).Value
            Application.EnableEvents = True
        End If
    End If

End Sub

I think this should do what you want.

 

[maabadi]

Hi Joe4
I think your code is correct but have errors and don't run is worksheet event.
I make a macro with yhis code and it show runtime error '424': object required.

Thanks

 

[Joe4]

Are you putting it in the proper sheet module so it will run automatically?
I copied your data and tested it out myself and it worked for me.

What are you doing when you get this error?
Which line of code does it highlight?
Did you copy/paste this code, or try typing it manually?
Do you have any merged or protected cells?
Are you trying to delete data on this sheet when the error occurs?

 

[maabadi]

Yes. I use Alt+F11 to open visual editor window. right click on sheet name in left panel and choose view code.
Then Copy and Paste your formula in right panel
I have this error in this Line:

"If Target.Count > 1 Then Exit Sub"

I don't have merged cell or protected sheet
I have another workbook change event for all sheets in workbook.
But I open another Excel file paste this numbers and convert B , D ,... to formulas then paste your code on it. then save as .xlsm format and change A5 or D5 but I didn't see anything and my formula exist in previous row yet.

 

[Joe4]

Please confirm for me the name of the Module you are placing the VBA code I gave you in.
Do you have any other "Worksheet_Change" VBA code in that same module?

I have another workbook change event for all sheets in workbook.

What is the name of the module this one is found in?
Can you post the VBA code for that?