问题描述:

I need to pull specific information from Excel Workbooks with multiple sheets in them. (Snapshot below). I imagine it would be easiest to pull each row then count the quantity from there. The main issue is there are multiple sections in a sheet. I don't care which language is used as long as I can get the info, preferably onto a new sheet at the end of the workbook, but doesn't have to be, I need to get the counts the ProServices need to put together their financial model.

img http://i67.tinypic.com/2s6rkma.png

Each section will always have a title. In the above example those are MEL and MEA. The title will be different but the formatting of that cell is always the same. What I need is the count of the main item (in this case is the 2nd row below title and is bold) from each section and then added together (I can always do the total quantity after the fact). If possible, I would like all the following info to be pulled and displayed on a new sheet.

[Sheet Name] [Title of Section] [Main Item] [Qty]

I can format the sheet how I want it to look unless there is a way to do that as well! Open for input on that one!

Please note, some sections might have multiple main items. However, they will always be bold and left indented and be in the 'Materials' portion.

网友答案:

The only assumption I made was that the indent for the listed materials was done with leading spaces. The code creates a new sheet renames the sheet, creates headers. The code then iterates through all cells in column a and fills an array accordingly, the first element is the name which is set first and changes when the next worksheet is selected. the second element contains the section name, which is set after a cell with the value "Materials" is detected, it is set with the Offset(-1, 0) since the section name is always followed by a cell with the value "Materials". Element 3 and 4 are set when a cell is with a value starting with either "WS" or "EDU" is detected. The entire array is then copied to the first available row on the output sheet.


Edit

The range to loop through consists of several parts:

'The range of cells to iterate through
Ws.Rang(first cell, last cell)
'First cell
Ws.Cells(rowindex, columnindex)
'The rowindex is the number of the row the cell is on. The formula "=ROW()" 
'in a cell on a worksheets shows the rowindex of that cell.
'The columnindex is the number of the column the cell is on, the formula "=COLUMN()"
'in a cell on a worksheet shows the columnindex of that cell.
'cell "A1" would be Ws.Cells(1, 1)
'Here is where it get tricky, to find the last cell
Ws.Rows.Count 'This returns the last rownumber available in the sheet
Ws.Cells(Ws.Rows.Count, 1) 'Refers to the last possible cell in the first column
Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row 
'This returns the rowindex of the first cell which contains data in first column looking
'from the bottom upwards.
Ws.Cells(Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row, 1)
'This refers to the first cell looking from the bottom up, in the first column.

Effectively this:

For Each Cell In Ws.Range(Ws.Cells(1, 1), Ws.Cells(Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row, 1))

should become

For Each Cell In Ws.Range(Ws.Cells(1, 2), Ws.Cells(Ws.Cells(Ws.Rows.Count, 2).End(xlUp).Row, 2))

Option Explicit

Sub CollectMainItems()
    Dim Ws As Worksheet, OutputWs As Worksheet
    Dim Cell As Range
    Dim TempArray As Variant
    Dim Prefix As String
    Dim NextRow As Long

    Dim Result(1 To 4) As Variant

    With ThisWorkbook.Worksheets
        Set OutputWs = .Add(, .Item(.Item(.Count).Name))
    End With
    With OutputWs
        .Name = "Output"
        .Cells(1, 1) = "Sheet Name"
        .Cells(1, 2) = "Section Title"
        .Cells(1, 3) = "Item Code"
        .Cells(1, 4) = "Quantity"
    End With

    For Each Ws In ThisWorkbook.Worksheets
        Result(1) = Ws.Name
        For Each Cell In Ws.Range(Ws.Cells(1, 2), Ws.Cells(Ws.Cells(Ws.Rows.Count, 2).End(xlUp).Row, 2))
            If Trim(Cell) = "Materials" Then
                Result(2) = Cell.Offset(-1, 0)
            End If
            If Not Cell = Empty Then
                TempArray = Split(Trim(Cell), "-")
                Prefix = TempArray(0)
                If Prefix = "EDU" Or Prefix = "WS" Then
                    Result(3) = Trim(Cell)
                    Result(4) = Cell.Offset(0, 5)
                    NextRow = OutputWs.Cells(OutputWs.Rows.Count, 1).End(xlUp).Row + 1
                    OutputWs.Range(OutputWs.Cells(NextRow, 1), OutputWs.Cells(NextRow, 4)) = Result
                End If
            End If
        Next Cell
    Next Ws

End Sub
相关阅读:
Top