Autofilter/Autopopulate

[ianfrancis56]

All,

I am new to the forum and relative new to VBA for excel. I am trying to populate a table based off of another table. The source table has labor categories in column G and, potentially, a cost figure in column Q. I want to check to see if there is a cost figure for each labor category and, if so, populate the second table with the labor category, but skip over it if there is no cost figure. I am probably way off, but it seems with this code I either get all or nothing. Thanks for any help!


Sub PEOFTECalc_Click()

Dim i%, LabCatCount%

LabCatCount = IsEmpty(Sheets("Labor").Range("Q" & i + 4))


For i = 1 To 31
If LabCatCount = True Then
Range("G" & i + 12) = Sheets("Labor").Range("G" & i + 4)
End If
Next i

End Sub

 

[Marcelo Branco]

Hi,

Welcome to the forum!

One question: both tables in the sheet Labor or in different sheets?

M.

 

[ianfrancis56]

Thanks! The Labor Category and Cost are in Sheets("Labor") and the the table is in a separate worksheet. As well, there is a formula in all of the cost category cells

 

[Marcelo Branco]

I'm not seeing your data (you havent post a sample...) so i'm following your original macro and in some way trying to guess , so please try the macro below in a test-workbook

Sub PEOFTECalc_Click()
    Dim i As Long
 
    With ActiveSheet
        For i = 1 To 31
            If .Range("Q" & i + 4) <> "" Then
                .Range("G" & i + 12) = Sheets("Labor").Range("G" & i + 4)
            End If
        Next i
    End With
End Sub

HTH

M.

 

[ianfrancis56]

Wow that almost has it! Amazing! I knew With had something to do with it, you'll have to forgive me, I have only been working with VBA for ~3 weeks now.

One more problem: How do I get rid of the space between the values? For example, the first result comes in and the next one is 10 rows farther down. I want it to show up right under the first result rather than 10 rows down. Any suggestions? Thanks again!

Pasted below is the code you gave me (one slight modification to make it work).

Sub PEOFTECalc_Click()

Dim i As Integer

With ActiveSheet
For i = 1 To 31
If Sheets("Labor").Range("Q" & i + 4) <> "" Then
.Range("G" & i + 12) = Sheets("Labor").Range("G" & i + 4)
End If
Next i
End With
End Sub

 

[Marcelo Branco]

If i understood correctly, try this

Sub PEOFTECalc_Click()
    Dim i As Long, lin As Long
 
    lin = 13
    With ActiveSheet
        For i = 1 To 31
            If .Range("Q" & i + 4) <> "" Then
                .Range("G" & lin) = Sheets("Labor").Range("G" & i + 4)
                lin = lin + 1
            End If
        Next i
    End With
End Sub

One advice: when posting code use the button #
It generates two tags

 and ['/CODE] without the ' and paste the code inside the tags
 
And always use indentation
 
M.

 

[ianfrancis56]

That was PERFECT! Thank you so much for helping a rookie, I will make fast work of catching up. Is there a text you recommend?

 

[Marcelo Branco]

You are welcome! Glad to help

Two excellent books:

http://www.amazon.com/VBA-Macros-Mi...r_1_10?s=books&ie=UTF8&qid=1313125175&sr=1-10

http://www.amazon.com/Excel-Power-P...=sr_1_4?s=books&ie=UTF8&qid=1313125175&sr=1-4

Good luck!

M.