I found this code on this NG and used it successfully several times.
But I have modified it slightly, to use a different formula and It returns
an error: Application Defined or Object Defined Error.

I don't know what this means.

This is the code which I sourced from Don Guillet:

Sub balance()' I use for a running balance in a checkbook.
Set frng = Range("b3:b" & Range("a65536").End(xlUp).Row)
With frng
..Formula= "vlookup(a1,sheet2!a2:z200,2,false)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value
End With
End Sub


This is my modified code:
Sub Balance()
Set frng = Range("L4:L" & Range("B65536").End(xlUp).Row)

With frng
..Formula = "=IF(K4=1,"",F4-D4)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value

End With

End Sub
I have used several successful variations to my version of the code, but
this formula "=IF(K4=1,"",F4-D4)" gives me the problem.

I don't know much about writing macros.
Can anyone offer an explanation and a fix for this please.
Cheers Paul

Try taking out the first or second '.' in

...Formula = "=IF(K4=1,"",F4-D4)"

Best of luck
DavidC

-----Original Message-----
I found this code on this NG and used it successfully
several times.
But I have modified it slightly, to use a different
formula and It returns
an error: Application Defined or Object Defined Error.

I don't know what this means.

This is the code which I sourced from Don Guillet:

Sub balance()' I use for a running balance in a checkbook.
Set frng = Range("b3:b" & Range("a65536").End(xlUp).Row)
With frng
..Formula= "vlookup(a1,sheet2!a2:z200,2,false)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value
End With
End Sub


This is my modified code:
Sub Balance()
Set frng = Range("L4:L" & Range("B65536").End(xlUp).Row)

With frng
..Formula = "=IF(K4=1,"",F4-D4)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value

End With

End Sub
I have used several successful variations to my version
of the code, but
this formula "=IF(K4=1,"",F4-D4)" gives me the problem.

I don't know much about writing macros.
Can anyone offer an explanation and a fix for this please.
Cheers Paul
.

Hi Paul,

You need to double the double quotes.

Replace:

.Formula = "=IF(K4=1,"",F4-D4)"

with

.Formula = "=IF(K4=1,"""",F4-D4)"


---
Regards,
Norman



"Paul" wrote in message
om...
I found this code on this NG and used it successfully several times.
But I have modified it slightly, to use a different formula and It returns
an error: Application Defined or Object Defined Error.

I don't know what this means.

This is the code which I sourced from Don Guillet:

Sub balance()' I use for a running balance in a checkbook.
Set frng = Range("b3:b" & Range("a65536").End(xlUp).Row)
With frng
.Formula= "vlookup(a1,sheet2!a2:z200,2,false)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value
End With
End Sub


This is my modified code:
Sub Balance()
Set frng = Range("L4:L" & Range("B65536").End(xlUp).Row)

With frng
.Formula = "=IF(K4=1,"",F4-D4)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value

End With

End Sub
I have used several successful variations to my version of the code, but
this formula "=IF(K4=1,"",F4-D4)" gives me the problem.

I don't know much about writing macros.
Can anyone offer an explanation and a fix for this please.
Cheers Paul

Thanks very much Norman.
That did the trick.

And thanks to DavidC for replying also.

Cheers Paul

"Norman Jones" wrote in message ...
Hi Paul,

You need to double the double quotes.

Replace:

.Formula = "=IF(K4=1,"",F4-D4)"

with

.Formula = "=IF(K4=1,"""",F4-D4)"


---
Regards,
Norman



"Paul" wrote in message
om...
I found this code on this NG and used it successfully several times.
But I have modified it slightly, to use a different formula and It returns
an error: Application Defined or Object Defined Error.

I don't know what this means.

This is the code which I sourced from Don Guillet:

Sub balance()' I use for a running balance in a checkbook.
Set frng = Range("b3:b" & Range("a65536").End(xlUp).Row)
With frng
.Formula= "vlookup(a1,sheet2!a2:z200,2,false)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value
End With
End Sub


This is my modified code:
Sub Balance()
Set frng = Range("L4:L" & Range("B65536").End(xlUp).Row)

With frng
.Formula = "=IF(K4=1,"",F4-D4)"
' .Formula = "=h7+d8"
.Formula = .Value 'changes the formula to value

End With

End Sub
I have used several successful variations to my version of the code, but
this formula "=IF(K4=1,"",F4-D4)" gives me the problem.

I don't know much about writing macros.
Can anyone offer an explanation and a fix for this please.
Cheers Paul