| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro autofills formula when a cell value is changed
I'm trying to set up a macro so that when a user alters the corresponding row
column D value, it auto-inserts the formula's into E, F and G columns to do the maths. This is so that if a user adds a row to the sheet they don't have to worry about copy pasting formulas etc. The below is what I have but nothing happens..... help! Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range(Target.Address), Range("A:E")) _ Is Nothing And Target.Row < 1 Then Dim r As Long r = Target.Row If Cells(r, "D").Value < "" Then 'Then ' the below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro autofills formula when a cell value is changed
Try this version...
Sub Worksheet_Change(ByVal Target As Range) Dim r As Long If Not Intersect(Range(Target.Address), Columns("D:D")) _ Is Nothing And Target.Row < 1 Then r = Target.Row If Len(Target.Value) 0 Then 'Then the code below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"""",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"""",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"""",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub Your formula for column G should be modified or you should change Target.Row<0 to Target.Row7, since it is trying to offset 7 rows up from the current row. Hope this helps, Hutch "raphiel2063" wrote: I'm trying to set up a macro so that when a user alters the corresponding row column D value, it auto-inserts the formula's into E, F and G columns to do the maths. This is so that if a user adds a row to the sheet they don't have to worry about copy pasting formulas etc. The below is what I have but nothing happens..... help! Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range(Target.Address), Range("A:E")) _ Is Nothing And Target.Row < 1 Then Dim r As Long r = Target.Row If Cells(r, "D").Value < "" Then 'Then ' the below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro autofills formula when a cell value is changed
Tom,
The macro doesn't even seem to run now. I've got security settings to allow all Macros to run and have disabled the line about rolumn G and it just won't run. Any ideas? Regards, Tom "Tom Hutchins" wrote: Try this version... Sub Worksheet_Change(ByVal Target As Range) Dim r As Long If Not Intersect(Range(Target.Address), Columns("D:D")) _ Is Nothing And Target.Row < 1 Then r = Target.Row If Len(Target.Value) 0 Then 'Then the code below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"""",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"""",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"""",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub Your formula for column G should be modified or you should change Target.Row<0 to Target.Row7, since it is trying to offset 7 rows up from the current row. Hope this helps, Hutch "raphiel2063" wrote: I'm trying to set up a macro so that when a user alters the corresponding row column D value, it auto-inserts the formula's into E, F and G columns to do the maths. This is so that if a user adds a row to the sheet they don't have to worry about copy pasting formulas etc. The below is what I have but nothing happens..... help! Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range(Target.Address), Range("A:E")) _ Is Nothing And Target.Row < 1 Then Dim r As Long r = Target.Row If Cells(r, "D").Value < "" Then 'Then ' the below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro autofills formula when a cell value is changed
I suspect that somewhere (in some other macro or event code) you are setting
Application.EnableEvents to FALSE. Paste this code in any module and run it: Sub AAAAA() Application.EnableEvents = True End Sub Then enter something in column D on the sheet with the Change event code. After you press Enter, check the cells in the same row in columns E, F, & G. If the Vlookup doesn't return anything your formulas display an empty string, so the cells look empty. Hope this helps, Hutch "raphiel2063" wrote: Tom, The macro doesn't even seem to run now. I've got security settings to allow all Macros to run and have disabled the line about rolumn G and it just won't run. Any ideas? Regards, Tom "Tom Hutchins" wrote: Try this version... Sub Worksheet_Change(ByVal Target As Range) Dim r As Long If Not Intersect(Range(Target.Address), Columns("D:D")) _ Is Nothing And Target.Row < 1 Then r = Target.Row If Len(Target.Value) 0 Then 'Then the code below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"""",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"""",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"""",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub Your formula for column G should be modified or you should change Target.Row<0 to Target.Row7, since it is trying to offset 7 rows up from the current row. Hope this helps, Hutch "raphiel2063" wrote: I'm trying to set up a macro so that when a user alters the corresponding row column D value, it auto-inserts the formula's into E, F and G columns to do the maths. This is so that if a user adds a row to the sheet they don't have to worry about copy pasting formulas etc. The below is what I have but nothing happens..... help! Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range(Target.Address), Range("A:E")) _ Is Nothing And Target.Row < 1 Then Dim r As Long r = Target.Row If Cells(r, "D").Value < "" Then 'Then ' the below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro autofills formula when a cell value is changed
Tom
I modified the macro as you mentioned but it's giving me a Run Time error 1004. Not even sure what that means but I have to either try to debug it or end the macro. Any ideas how to fix this? I'm not sure what it means so not sure what to do. "Target.Row<0 to Target.Row7, since it is trying to offset 7 rows up from the current row." Thanks again. "Tom Hutchins" wrote: I suspect that somewhere (in some other macro or event code) you are setting Application.EnableEvents to FALSE. Paste this code in any module and run it: Sub AAAAA() Application.EnableEvents = True End Sub Then enter something in column D on the sheet with the Change event code. After you press Enter, check the cells in the same row in columns E, F, & G. If the Vlookup doesn't return anything your formulas display an empty string, so the cells look empty. Hope this helps, Hutch "raphiel2063" wrote: Tom, The macro doesn't even seem to run now. I've got security settings to allow all Macros to run and have disabled the line about rolumn G and it just won't run. Any ideas? Regards, Tom "Tom Hutchins" wrote: Try this version... Sub Worksheet_Change(ByVal Target As Range) Dim r As Long If Not Intersect(Range(Target.Address), Columns("D:D")) _ Is Nothing And Target.Row < 1 Then r = Target.Row If Len(Target.Value) 0 Then 'Then the code below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"""",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"""",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"""",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub Your formula for column G should be modified or you should change Target.Row<0 to Target.Row7, since it is trying to offset 7 rows up from the current row. Hope this helps, Hutch "raphiel2063" wrote: I'm trying to set up a macro so that when a user alters the corresponding row column D value, it auto-inserts the formula's into E, F and G columns to do the maths. This is so that if a user adds a row to the sheet they don't have to worry about copy pasting formulas etc. The below is what I have but nothing happens..... help! Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range(Target.Address), Range("A:E")) _ Is Nothing And Target.Row < 1 Then Dim r As Long r = Target.Row If Cells(r, "D").Value < "" Then 'Then ' the below adds in the formula in column E, F and G Cells(r, "E").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE)),"",VLOOKUP(RC[-2],'FFELibrary'!C[-3]:C[-1],2,FALSE))" Cells(r, "F").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE)),"",VLOOKUP(RC[-3],'FFELibrary'!C[-4]:C[-2],3,FALSE))" Cells(r, "G").FormulaR1C1 = "=IF(ISERROR(IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3]))),"",IF(RC[-1]=0,RC[-2]*RC[-3],IF(R201C4=Sheet1!R[-7]C[-5],RC[-1]*RC[-3],RC[-2]*RC[-3])))" End If End If Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula that autofills by trend rather than linear | Excel Discussion (Misc queries) | |||
| A formula that autofills by trend, not linear | Excel Discussion (Misc queries) | |||
| run macro whenever any cell is changed | Excel Programming | |||
| Excell formula that autofills information | New Users to Excel | |||
| Run Macro when particular cell is changed? | Excel Programming | |||
Linear Mode
