Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am back.

    I have completed the template for 4 carriers. I have huge macros to populate them. I need to know if there is an easier way to write the macros.

    I have attached the two wookbooks that are needed to run the macros. The carriers that I have completed are Builder Bene 08, Capital Blue Cross, First Health and keystone. The RX macros are complete for these also.

    Hope someone can chime in on this.

    [attachment=83766:CostAnalysis.zip

    Thanks,
    Cindy
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='Honeyrun' post='775363' date='15-May-2009 07:40']I have completed the template for 4 carriers. I have huge macros to populate them. I need to know if there is an easier way to write the macros.

    I have attached the two wookbooks that are needed to run the macros. The carriers that I have completed are Builder Bene 08, Capital Blue Cross, First Health and keystone. The RX macros are complete for these also.[/quote]
    Is this a continuation from your Post 769066? Stuart and Rory may not be around today. and are best suited to help since they worked with you before. It would help other Loungers if you explain the purpose of the worksheets, as these are substantial efforts. A question and a comment:

    Why not combine the two workbooks into one? That way you don't need to jump from workbook to workbook (and without testing it looks like your macros will fail if Library.xls is not open).

    Your code was recorded from Macros, and can be improved by collapsing many of the lines, for example if all three sheets were in one workbook:

    Windows("Library.xls").Activate
    Sheets("Builder's Bene 2008").Select
    Range("E26:F30").Select
    Selection.Copy
    Windows("MasterX2.xls").Activate
    Sheets("Single").Select
    Range("E33:F33").Select
    ActiveSheet.Paste

    Will run faster and be simpler if stated as:

    Sheets("Builder's Bene 2008").Range("E26:F30").Copy
    Sheets("Single").Range("E33:F33").Paste

    Also, to stop the flickering of the worksheet display, use Application.ScreenUpdating = False at the beginning of each sub, and Application.ScreenUpdating = True at the end of each sub

    There's more, and other Loungers will give further advice, but that should get you started.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi!

    further to John's reply, a quick glance indicates that 1) this is a complicated little system you've got here; and 2) yes, you could make life easer for yourself.

    For example, you've got some code that is repeated for the 40+ different values of E5:

    Code:
     
    If Range("E4") = "Builder's Bene 2008" And Range("E5") = "PPO Plus" Then
    	Windows("Library.xls").Activate
    	Sheets("Builder's Bene 2008").Select
    	Range("E4:F18").Select
    	Selection.Copy
    	Windows("MasterX2.xls").Activate
    	Sheets("Single").Select
    	Range("E17:F17").Select
    	ActiveSheet.Paste
    End If
    As well as simplifying the code as described, you could use a function to carry out the copying, passing the variable element (the range to copy, in this case) as a parameter to the function. Since it seems that only one of your IF statements would ever be true, you could use a Select Case statement instead of multiple IF statements.

    If the value in E4 always equates to the name of the target sheet, you could again pass this value as a parameter to the function rather than test for it each time. Other methods of dealing with this situation are available!

    A couple of small suggestions, I'm sure others will have more to offer.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='JulesG' post='775369' date='15-May-2009 07:44']If the value in E4 always equates to the name of the target sheet, you could again pass this value as a parameter to the function rather than test for it each time. Other methods of dealing with this situation are available![/quote]
    "Piling on" to Jules ideas (he spent more time looking at the code than I did), anytime you have a source cell or value that code repeatedly refers to, you can Dim it as an appropriate Data Type and refer to it in code, such as:

    Sub Example
    Dim strSourceCellVal as String

    strSourceCellval = ThisWorkbook.Worksheets("Single").Range("E4").Valu e

    Select Case strSourceCellVal
    Case "Capital Blue Cross"
    ...<do or set something>

    (It was clear that your code was all recorded because no variables are declared (Dim'd) and there are no loops or select case statements, which the macro recorder cannot create; problem is that you need to learn bit by bit, we can't "feed you all the hay in the barn". Search for Excel VBA book recommendations in this Forum, and see also Chip Pearson on some good VBA fundamentals at http://www.cpearson.com/excel/DeclaringVariables.aspx and http://www.cpearson.com/excel/optimize.htm)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the suggestions. I did take an Excel VBA class some 6 years ago and haven't used it since. It is slowly coming back. Your suggesstions are getting the brain cells working again. I didn't record the code, but I am doing it piece by piece with the easist (read as beginners) to write.

    Thanks again.

    Cindy

Posting Permissions

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