Macro autofills formula when a cell value is changed

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
 
T

Tom Hutchins

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
 
R

raphiel2063

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...
 
T

Tom Hutchins

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...
 
R

raphiel2063

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Formula in macro gives "Run-time error '1004':" 1
Formula appearing instead of result 2
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Insert row gives run-time error 13 message 1
macro to copy rows that contain a cell with a certain number 3
display calculation next row 5

Top