+ Reply to Thread
Results 1 to 7 of 7

How can I shorten this VBA code?

  1. 01-21-2010, 03:40 PM #1
    hemi_fan
    hemi_fan is offline
    Registered User
    Join Date
    01-18-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    How can I shorten this VBA code?

    Here's the code that I'm using. It performs 91 iterated functions but I feel as though I can write this a better way. How can I shorten it so it's more reasonable?

    If Target.Address = "$B$1" Then
    On Error GoTo ErrorTrap:
    Range("B30").GoalSeek Goal:=0, ChangingCell:=Range("B18")
    Range("K47").GoalSeek Goal:=0, ChangingCell:=Range("C47")
    Range("K48").GoalSeek Goal:=0, ChangingCell:=Range("C48")
    Range("K49").GoalSeek Goal:=0, ChangingCell:=Range("C49")
    Range("K50").GoalSeek Goal:=0, ChangingCell:=Range("C50")
    Range("K51").GoalSeek Goal:=0, ChangingCell:=Range("C51")
    Range("K52").GoalSeek Goal:=0, ChangingCell:=Range("C52")
    Range("K53").GoalSeek Goal:=0, ChangingCell:=Range("C53")
    Range("K54").GoalSeek Goal:=0, ChangingCell:=Range("C54")
    Range("K55").GoalSeek Goal:=0, ChangingCell:=Range("C55")
    Range("K56").GoalSeek Goal:=0, ChangingCell:=Range("C56")
    Range("K57").GoalSeek Goal:=0, ChangingCell:=Range("C57")
    Range("K58").GoalSeek Goal:=0, ChangingCell:=Range("C58")
    Range("K59").GoalSeek Goal:=0, ChangingCell:=Range("C59")
    Range("K60").GoalSeek Goal:=0, ChangingCell:=Range("C60")
    Range("K61").GoalSeek Goal:=0, ChangingCell:=Range("C61")
    Range("K62").GoalSeek Goal:=0, ChangingCell:=Range("C62")
    Range("K63").GoalSeek Goal:=0, ChangingCell:=Range("C63")
    Range("K64").GoalSeek Goal:=0, ChangingCell:=Range("C64")
    Range("K65").GoalSeek Goal:=0, ChangingCell:=Range("C65")
    Range("K66").GoalSeek Goal:=0, ChangingCell:=Range("C66")
    Range("K67").GoalSeek Goal:=0, ChangingCell:=Range("C67")
    Range("K68").GoalSeek Goal:=0, ChangingCell:=Range("C68")
    Range("K69").GoalSeek Goal:=0, ChangingCell:=Range("C69")
    Range("K70").GoalSeek Goal:=0, ChangingCell:=Range("C70")
    Range("K71").GoalSeek Goal:=0, ChangingCell:=Range("C71")
    Range("K72").GoalSeek Goal:=0, ChangingCell:=Range("C72")
    Range("K73").GoalSeek Goal:=0, ChangingCell:=Range("C73")
    Range("K74").GoalSeek Goal:=0, ChangingCell:=Range("C74")
    Range("K75").GoalSeek Goal:=0, ChangingCell:=Range("C75")
    Range("K75").GoalSeek Goal:=0, ChangingCell:=Range("C75")
    Range("K76").GoalSeek Goal:=0, ChangingCell:=Range("C76")
    Range("K77").GoalSeek Goal:=0, ChangingCell:=Range("C77")
    Range("K78").GoalSeek Goal:=0, ChangingCell:=Range("C78")
    Range("K79").GoalSeek Goal:=0, ChangingCell:=Range("C79")
    Range("K80").GoalSeek Goal:=0, ChangingCell:=Range("C80")
    Range("K81").GoalSeek Goal:=0, ChangingCell:=Range("C81")
    Range("K82").GoalSeek Goal:=0, ChangingCell:=Range("C82")
    Range("K83").GoalSeek Goal:=0, ChangingCell:=Range("C83")
    Range("K84").GoalSeek Goal:=0, ChangingCell:=Range("C84")
    Range("K85").GoalSeek Goal:=0, ChangingCell:=Range("C85")
    Range("K86").GoalSeek Goal:=0, ChangingCell:=Range("C86")
    Range("K87").GoalSeek Goal:=0, ChangingCell:=Range("C87")
    Range("K88").GoalSeek Goal:=0, ChangingCell:=Range("C88")
    Range("K89").GoalSeek Goal:=0, ChangingCell:=Range("C89")
    Range("K90").GoalSeek Goal:=0, ChangingCell:=Range("C90")
    Range("K91").GoalSeek Goal:=0, ChangingCell:=Range("C91")
    Range("K92").GoalSeek Goal:=0, ChangingCell:=Range("C92")
    Range("K93").GoalSeek Goal:=0, ChangingCell:=Range("C93")
    Range("K94").GoalSeek Goal:=0, ChangingCell:=Range("C94")
    Range("K95").GoalSeek Goal:=0, ChangingCell:=Range("C95")
    Range("K96").GoalSeek Goal:=0, ChangingCell:=Range("C96")
    Range("K97").GoalSeek Goal:=0, ChangingCell:=Range("C97")
    Range("K98").GoalSeek Goal:=0, ChangingCell:=Range("C98")
    Range("K99").GoalSeek Goal:=0, ChangingCell:=Range("C99")
    Range("K100").GoalSeek Goal:=0, ChangingCell:=Range("C100")
    Range("K101").GoalSeek Goal:=0, ChangingCell:=Range("C101")
    Range("K102").GoalSeek Goal:=0, ChangingCell:=Range("C102")
    Range("K103").GoalSeek Goal:=0, ChangingCell:=Range("C103")
    Range("K104").GoalSeek Goal:=0, ChangingCell:=Range("C104")
    Range("K105").GoalSeek Goal:=0, ChangingCell:=Range("C105")
    Range("K106").GoalSeek Goal:=0, ChangingCell:=Range("C106")
    Range("K107").GoalSeek Goal:=0, ChangingCell:=Range("C107")
    Range("K108").GoalSeek Goal:=0, ChangingCell:=Range("C108")
    Range("K109").GoalSeek Goal:=0, ChangingCell:=Range("C109")
    Range("K110").GoalSeek Goal:=0, ChangingCell:=Range("C110")
    Range("K111").GoalSeek Goal:=0, ChangingCell:=Range("C111")
    Range("K112").GoalSeek Goal:=0, ChangingCell:=Range("C112")
    Range("K113").GoalSeek Goal:=0, ChangingCell:=Range("C113")
    Range("K114").GoalSeek Goal:=0, ChangingCell:=Range("C114")
    Range("K115").GoalSeek Goal:=0, ChangingCell:=Range("C115")
    Range("K116").GoalSeek Goal:=0, ChangingCell:=Range("C116")
    Range("K117").GoalSeek Goal:=0, ChangingCell:=Range("C117")
    Range("K118").GoalSeek Goal:=0, ChangingCell:=Range("C118")
    Range("K119").GoalSeek Goal:=0, ChangingCell:=Range("C119")
    Range("K120").GoalSeek Goal:=0, ChangingCell:=Range("C120")
    Range("K121").GoalSeek Goal:=0, ChangingCell:=Range("C121")
    Range("K122").GoalSeek Goal:=0, ChangingCell:=Range("C122")
    Range("K123").GoalSeek Goal:=0, ChangingCell:=Range("C123")
    Range("K124").GoalSeek Goal:=0, ChangingCell:=Range("C124")
    Range("K125").GoalSeek Goal:=0, ChangingCell:=Range("C125")
    Range("K126").GoalSeek Goal:=0, ChangingCell:=Range("C126")
    Range("K127").GoalSeek Goal:=0, ChangingCell:=Range("C127")
    Range("K128").GoalSeek Goal:=0, ChangingCell:=Range("C128")
    Range("K129").GoalSeek Goal:=0, ChangingCell:=Range("C129")
    Range("K130").GoalSeek Goal:=0, ChangingCell:=Range("C130")
    Range("K131").GoalSeek Goal:=0, ChangingCell:=Range("C131")
    Range("K132").GoalSeek Goal:=0, ChangingCell:=Range("C132")
    Range("K133").GoalSeek Goal:=0, ChangingCell:=Range("C133")
    Range("K134").GoalSeek Goal:=0, ChangingCell:=Range("C134")
    Range("K135").GoalSeek Goal:=0, ChangingCell:=Range("C135")
    Range("K136").GoalSeek Goal:=0, ChangingCell:=Range("C136")
    Range("K137").GoalSeek Goal:=0, ChangingCell:=Range("C137")


    End If
    Last edited by hemi_fan; 01-21-2010 at 03:48 PM.
    Reply With QuoteRegister To Reply
  2. 01-21-2010, 04:38 PM #2
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: How can I shorten this VBA code?

    Does this work?

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
  3. 01-21-2010, 05:00 PM #3
    hemi_fan
    hemi_fan is offline
    Registered User
    Join Date
    01-18-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How can I shorten this VBA code?

    Quote Originally Posted by davegugg View Post
    Does this work?

    Please Login or Register  to view this content.
    Interesting. This seems to do what it's supposed to do, however it caused another part of the program to malfunction and I'm not sure why.

    **Edit**
    I stand corrected. It is not working properly.
    Last edited by hemi_fan; 01-21-2010 at 05:07 PM.
    Reply With QuoteRegister To Reply
  4. 01-21-2010, 05:39 PM #4
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: How can I shorten this VBA code?

    Hmm, I've never used the goalseek function, so I'm not familiar. How is it not functioning properly?
    Reply With QuoteRegister To Reply
  5. 01-22-2010, 05:51 AM #5
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: How can I shorten this VBA code?

    Maybe more like

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info
    Reply With QuoteRegister To Reply
  6. 01-22-2010, 10:29 AM #6
    hemi_fan
    hemi_fan is offline
    Registered User
    Join Date
    01-18-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How can I shorten this VBA code?

    In the first case it's a runtime error 1004, 'Reference is not valid' for the Range(Cells(i, 11)).GoalSeek Goal:=0, ChangingCell:=Range(Cells(i, 3)) line.

    In the second case its a runtime error 1004, application defined or object-defined error.
    Reply With QuoteRegister To Reply
  7. 01-22-2010, 11:53 AM #7
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: How can I shorten this VBA code?

    Then you need to post a sample workbook so we can see exactly what is going on.
    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