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

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

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

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

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