Request for some insite/advice on my VBA code

043792 · Aug 31st 2005

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")

**norie** · Aug 31st 2005

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

That piece of code does not 'poke' the value of FINDER into A36.

It creates a reference(FINDER) to A36 on the TOTALS sheet.

043792 · Aug 31st 2005

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

Code

Private Sub CommandButton2_Click()


    
    Dim I As Long
    Dim j As Long
    Dim n As Long
    
    Dim rng As Range
    Dim FINDER As Range
    Dim prtLkup As Range
    Dim LabLkup As Range
    
    On Error Resume Next
    ' needed when vlookup encounters a search value that is not present
    
        'clear the contents to the Totals sheet prior to new build
        Sheets("TOTALS").Range("A2:M100").Clear
    
    
    'populates the totals sheet with date across top and machines down
    Set rng = Sheets("Ad_indices_labour_2005").Range("A5")
    
    Sheets("Ad_indices_labour_2005").Select
    Sheets("Ad_indices_labour_2005").Range("b3:M3").Copy
            Sheets("TOTALS").Range("b1:M1").PasteSpecial xlPasteValues
    
    I = 5
    
    
    While rng.Value <> ""
        Sheets("Ad_indices_labour_2005").Select
            Sheets("Ad_indices_labour_2005").Range("A" & I).Copy
            Sheets("TOTALS").Range("A" & I - 2).PasteSpecial xlPasteValues
I = I + 1
Set rng = Sheets("Ad_indices_labour_2005").Range("A" & I)
    Wend
    
    'this section looks for the machine on the totals sheet and adds
    'the part cost from the 'Ad_parts' sheet to the
    'labour cost from the 'Ad_indices_labour_2005' sheet
    
    '***** why does the following line poke the value of "FINDER" into cell A36?
    
    Set FINDER = Sheets("TOTALS").Range("A36")
    Set prtLkup = Sheets("Ad_parts").Range("A1:M100")
    Set LabLkup = Sheets("Ad_indices_labour_2005").Range("o3:AA100")
 For j = 3 To (I - 3)
 FINDER = Sheets("TOTALS").Range("A" & j)
 For n = 1 To 12
    part = Application.WorksheetFunction.VLookup(FINDER, prtLkup, n + 1, False)
    lab = Application.WorksheetFunction.VLookup(FINDER, LabLkup, n + 1, False)
    Sheets("TOTALS").Range("A" & j).Offset(0, n) = part + lab
Next
Next
   
   
End Sub

Display More

043792 · Aug 31st 2005

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

Joebbshop · Sep 1st 2005

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.

Code

Sub CreateTotalSheet()
    Dim LastRowHeader As Range
    Dim i As Integer
    Dim j As Long
    
    Dim prtLkup As Range
    Dim PartValue As Single
    Dim LabLkup As Range
    Dim LabourValue As Single
    
    Application.ScreenUpdating = False
    
    'Clear out the old Totals table
    Sheets("Totals").Cells.Clear
    
    'Put the column headers in the Totals sheet
    Sheets("Ad_indices_labour_2005").Range("B3:M3").Copy
    Sheets("Totals").Range("B1").PasteSpecial xlPasteValues
        
    'Put the row headers in the Total sheet
    Sheets("Ad_indices_labour_2005").Select
    Range("A5").Select
    Set LastRowHeader = ActiveCell.End(xlDown)
    
    Range(ActiveCell.Address & ":" & LastRowHeader.Address).Copy
    Sheets("Totals").Range("A2").PasteSpecial xlPasteValues
    
    
    'Populate the table values
    Set prtLkup = Sheets("Ad_parts").Range("A1:M100")
    Set LabLkup = Sheets("Ad_indices_labour_2005").Range("O3:AA100")
    Sheets("Totals").Select
    Range("A1").Select
    
    On Error Resume Next
    For j = 2 To Range("A2").End(xlDown).Row
        For i = 2 To 13
            
            PartValue = Application.WorksheetFunction.VLookup _
                (Cells(j, 1).Value, prtLkup, i, False)
            If Err.Number > 0 Then
                PartValue = 0
                Err.Number = 0
            End If
            
            LabourValue = Application.WorksheetFunction.VLookup _
                (Cells(j, 1).Value, LabLkup, i, False)
            If Err.Number > 0 Then
                LabourValue = 0
                Err.Number = 0
            End If
            
            Cells(j, i).Formula = "=" & PartValue _
                & "+" & LabourValue
        Next i
    Next j
    
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Display More

Joebbshop · Sep 1st 2005

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)

043792 · Sep 1st 2005

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

043792 · Sep 1st 2005

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

Request for some insite/advice on my VBA code

Participate now!

Share