Wrox Programmer Forums
|
BOOK: Excel 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2003 VBA Programmer's Reference by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg, Brian Patterson; ISBN: 9780764556609
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2009, 04:15 PM
Registered User
 
Join Date: Dec 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Insert formula

i was recently using the following vba code to insert a formula and it worked fine but i've had to change the formula and the new formula wont work, just says 'Application-defined or Object defined error.

I believe it's because the new formula contains a custom function but am unsure.

Old working code:
Range("O12").Formula = "=(IF(ISBLANK(C12),"""",IF(ISERROR(VLOOKUP(C12,IND IRECT(""'""&($AG$2-1)&""'!$C:$AG""),13,FALSE)),N12,VLOOKUP(C12,INDIRE CT(""'""&($AG$2-1)&""'!$C:$AG""),13,FALSE))))"

New Not working code:
Range("O12").Formula = "=IF(ISBLANK(C12),"",IF(ISERROR(VLOOKUP(C12,INDIRE CT(PrevSheetName()&""!$C:$AG""),13,FALSE)),N12,VLO OKUP(C12,INDIRECT(PrevSheetName()&""!$C:$AG""),13, FALSE))))"

Last note:
The new formula does work when manually entered into the required cell but fails when vba is instructed to put the formula into a cell.

Any help would be appreciated, this is the last element of my workbook then it's complete.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Translating WorkSheet Formula in VBA AndyL Beginning VB 6 2 June 11th, 2008 04:13 AM
Excel formula not working in vba yogeshyl Excel VBA 2 December 12th, 2007 02:27 AM
Excel formula not working in VBA yogeshyl Excel VBA 2 July 19th, 2007 03:57 AM
Insert Image Using VBA timmaher Excel VBA 1 September 3rd, 2004 10:54 AM
Problems introducing a formula using VBA mariona_cid Excel VBA 0 June 1st, 2004 07:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.