Request for some insite/advice on my VBA code

  • Would really appreciate some help with this, I am kinda new to VBA but I have managed to get the sheet to mostly work :?
    I have attached a sample sheet of what I am trying to do.
    Basically I am building row and column heads on TOTALS sheet using data from the labour sheet. The row headers can change from month to month and will change between various functions in the company.
    The TOTALS sheet data is a sum based on row headers across the parts and labour sheets.
    Hope seeing the example make those words clearer.
    I have added comment to the code to help explain what I am doing
    Here is my problem, If I push the button on the start page to build my totals page it goes of and executes the code and builds the sheet but for some reason it seems to loop back to the begining and erase the sheet ????
    If I Step the code in debug the sheet builds properly.
    There is also this question in the code ...

    Code
    '***** why does the following line poke the value of "FINDER" into cell A36?
        
        Set FINDER = Sheets("TOTALS").Range("A36")
  • Re: Request for some insite/advice on my VBA code


    Thanks for looking ... Thats what i think it should do as well, but try the example, run it in debug and you will see the value of a3 gets poped into a36 when FINDER is reset to A3 in the loop. The value in A36 changes as the loop progresses to the value of A3 thru A31. Must be someting I have done but I cant see it.
    I had originally done

    Code
    Set FINDER = Sheets("TOTALS").Range("A3")


    but found that my header in A3 kept changing so I moved it to A36 out of the range that I care about


    Here is the entire VBA code


  • Re: Request for some insite/advice on my VBA code


    Feeling a bit smarter and rather embarrased. Had a old chunk of code sitting on another sheet that was erasing my totals sheet so the only issue I still have is the value that appears in A36 on the totals sheet.
    I have attached the revised code, also feel free to make suggestions on improving my coding. TIA

  • Re: Request for some insite/advice on my VBA code


    I'm fairly new to VBA also, so take this with a grain of salt. Here are my thoughts.


    When running the code a number is put ("Poked" ? :) ) into cell A36 because of the combination of these lines of code...


    Code
    Set FINDER = Sheets("TOTALS").Range("A36")
    ...
     FINDER = Sheets("TOTALS").Range("A" & j)


    In the first line copied above, you are essentially temporarily "naming" the cell A36 as "FINDER". Whatever happens to FINDER happens to cell A36. In the second line copied above, you are changing FINDER to equal the range "A3" the first time the line of code is run... it's 3 because of the value of j is 3 at that point. A simple modification to your code to prevent this is to remove the first line of code totally - Don't need to set FINDER before you get into the For loop. Then, in the second line type "Set" before the "FINDER = ..."


    Here's how I would write the code. No promises that it's an improvement, just another way to think about things.


  • Re: Request for some insite/advice on my VBA code


    also, I don't think you need all that math out there in columns O through AA. If you make these changes, you can just delete the extra table in columns O through AA.


    Put this before the for loop


    Code
    LabourRate = Sheets("Ad_indices_labour_2005").Range("A2")


    and this in place of the other lines of code


    Code
    Set LabLkup = Sheets("Ad_indices_labour_2005").Range("A3:m100")
    
    
    LabourValue = LabourRate * Application.WorksheetFunction.VLookup _
                    (Cells(j, 1).Value, LabLkup, i, False)
  • Re: Request for some insite/advice on my VBA code


    Thanks for looking. I like your way of doing this.
    I was just experimenting with the End(xlDown) command and find it very useful.
    The extra table was in existance when they turned this project over to me, apperently they need to see the table data

  • Re: Request for some insite/advice on my VBA code


    Trying out your code...
    any idea why you get a Run-time error '1004'
    Select Method of Range Class Failed
    on the following lines of code


    Code
    Range("A5").Select


    and

    Code
    Range("A1").Select


    You can get around it by adding the sheet reference in front but not sure why that is necessary

    Code
    Sheets("Ad_indices_labour_2005").Range("A5").Select

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!