Macro autofills formula when a cell value is changed

  • Thread starter Thread starter raphiel2063
  • Start date Start date
R

raphiel2063

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
 
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.Row>7, since it is trying to offset 7 rows up from
the current row.

Hope this helps,

Hutch
 
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 said:
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.Row>7, since it is trying to offset 7 rows up from
the current row.

Hope this helps,

Hutch

raphiel2063 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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.Row>7, since it is trying to offset 7 rows up from
the current row.

Hope this helps,

Hutch

raphiel2063 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.Row>7, since it is trying to offset 7 rows up from
the current row."

Thanks again.



Tom Hutchins said:
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 said:
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 said:
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.Row>7, since it is trying to offset 7 rows up from
the current row.

Hope this helps,

Hutch

:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top