+ Reply to Thread
Results 1 to 18 of 18

VBA code for formula

  1. 04-05-2010, 11:08 AM #1
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    VBA code for formula

    Hello everyone. I have followed many of the suggestions that were given to me w.r.t an automation I am trying to develope. But most resulted in fails.

    I am using the following excel formula within a worksheet with approximately 20,000 records. 

    Please Login or Register  to view this content.
    Originally I was going to paste the data necessary to compute the formula into the worksheet, and allow the formulas to automatically "update" after pasting. This is causing the macro to crash [ white screens, not responding etc.] This formula resides in column AC.

    Is it possible that an actual VBA code to compute the formula as oppsed to an excel formula in the sheet will speed things up? Maybe a different excel formula? The Find_Method?

    Any suggestions? I do not want to use access.

    I can post the entire VBA code for the project if necessary.
    Last edited by AnthonyWB; 04-05-2010 at 04:02 PM.
    Reply With QuoteRegister To Reply
  2. 04-05-2010, 11:17 AM #2
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    Rather than performing the Vlookup on the whole column which I think will be very expensive calculation wise you might want to define the last row and determine the range to look at.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  3. 04-05-2010, 11:24 AM #3
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula....one more time

    I'll give that a go, I doubt it will solve the problem. Below is the entire macro, Any improvements would be appreciated. 


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 04-05-2010, 11:35 AM #4
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    Avoiding the use of Select which is practically never needed in VBA and turning off screen updating can make a big difference as well:

    Please Login or Register  to view this content.

    Dom
    Reply With QuoteRegister To Reply
  5. 04-05-2010, 11:48 AM #5
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula....one more time

    I tried, and I ran your macro. An error occcurs stating that that object does not support this property or method. Everyone tells me to get rid of the .Select, and when I do I have issues compiling the code. 

    Please Login or Register  to view this content.
    where the "RANGE("A1").Paste is highlighted in yellow. By adding ".Select" it works. Why?
    Reply With QuoteRegister To Reply
  6. 04-05-2010, 12:02 PM #6
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    Try:

    Please Login or Register  to view this content.

    Dom
    Reply With QuoteRegister To Reply
  7. 04-05-2010, 12:10 PM #7
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula....one more time

    Is it just me, or is that the same code?
    Reply With QuoteRegister To Reply
  8. 04-05-2010, 12:12 PM #8
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    There's an extra .
    Reply With QuoteRegister To Reply
  9. 04-05-2010, 12:21 PM #9
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula....one more time

    Nope same error as before. There must be something wierd going on. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 04-05-2010, 12:26 PM #10
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    I'm with you on that one. Can you upload a sample workbook. Have been in the pub for the last 4 days practically so I might be missing the obvious...
    Reply With QuoteRegister To Reply
  11. 04-05-2010, 12:28 PM #11
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA code for formula....one more time

    There is no Paste method for a range; it's ActiveSheet.Paste or Range.PasteSpecial
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  12. 04-05-2010, 12:34 PM #12
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula....one more time

    Doh! Think I might log off for the rest of the day to save further embarrassment...

    Dom
    Reply With QuoteRegister To Reply
  13. 04-05-2010, 01:09 PM #13
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula

    The following snippet works also:

    Please Login or Register  to view this content.
    Anyother suggestions for tightning up the code?
    Reply With QuoteRegister To Reply
  14. 04-05-2010, 01:28 PM #14
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code for formula

    One tip would be instead of using Copy...PasteSpecialValues you can just write directly like this:

    Please Login or Register  to view this content.

    I would also maybe define your workbooks as objects, makes it easier to deal with when you are working with multiple workbooks.

    Dom.
    Reply With QuoteRegister To Reply
  15. 04-05-2010, 01:40 PM #15
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula

    "I would also maybe define your workbooks as objects, makes it easier to deal with when you are working with multiple workbooks."

    Can you elaborate on this?
    Reply With QuoteRegister To Reply
  16. 04-05-2010, 01:51 PM #16
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula

    I am getting an error in submacro2 , perhaps I am not implementing it correctly. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  17. 04-05-2010, 03:37 PM #17
    AnthonyWB
    AnthonyWB is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: VBA code for formula

    By selecting that the file update only before closing, the performance of the macro is greatly increased.
    Reply With QuoteRegister To Reply
  18. 04-05-2010, 05:13 PM #18
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA code for formula

    The following snippet works also:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1