Select cell next to certain text

[garyi]

Hello. sorry I will try and explain what I want to do.

I have a workbook of recipe costings, I want to just pull the portion cost from each sheet. The text in the cell to the left is 'Planned Individual Portion Cost' with a cost in the right cell. I just want to list those costs in another sheet, ideally with the name of the tab they are in as well.

I am guessing this would need a macro of some kind not really sure how to start.

Problem is the price whilst always in column G, may appear on any line.

Can anyone help me?

Cheers

 

[bebo021999]

Yes of course it requires VBA.
Anyway, could you post an image or screenshot or minisheet with data and describe clearly what you expect to get?

 

[garyi]

Hi there, here is a screewn grab of one tab. It occurs to me thgat the name of the dish appears in cell C1 on each sheet which may simplify things

Its a very basic work book, there are multiple tabs, some tabs dont have this info though. I want the name of the dish C1, then the planned cost in this case on G16, but that cell can vary depending ont he amount of ingredients.

I want to have a summary sheet with this info listed for worksheet.

 

[hungtbatman1]

Try it

Function cell_cost(ByVal findtext As String, Optional ByVal colwithcost As String = "G") As Range
'    colwithcost: the name of the column where Cost is
'   findtext: the text in the cell on the same row as cost and in the column before the colwithcost column
'   the function will return the cell (RANGE) with cost.
'    The code will search each sheet and return the first result in the first sheet.
Dim i As Long, cell_ As Range, sh As Worksheet
    For i = 1 To ThisWorkbook.Worksheets.Count
        Set sh = ThisWorkbook.Worksheets(i)
        With sh.Range("A1:" & colwithcost & Rows.Count)
            Set cell_ = .Find(findtext, , xlValues, xlWhole, xlByRows, xlNext)
            If Not cell_ Is Nothing Then
                If cell_.Offset(1 - cell_.Row, 1).Address(False, False) = UCase(colwithcost) & 1 Then   ' cell_ is in the column before the column colwithcost
                    Set cell_cost = cell_.Offset(, 1)
                    Exit For
                End If
            End If
        End With
    Next i
End Function

Sub example()
'    example of operation with cell (RANGE) returned by cell_cost function
Dim cell_ As Range
    Set cell_ = cell_cost("Planned Individual Portion Cost", "G")
    If Not cell_ Is Nothing Then    ' cell is found
        Debug.Print "cell is on sheet named: " & cell_.Parent.Name
        Debug.Print "address cell: " & cell_.Address
'        ---- Do anything with cell_
    End If
End Sub

 

[garyi]

Thank you I will give that a go when I am back at my pc.

 

[Alex Blakenburg]

Assuming you have a summary sheet already set up called summary which is either blank or has the headings in A1, here is another option.

Sub GetTotals()

    Dim destWS As Worksheet
    Dim srcWS As Worksheet
    Dim destNextRow As Long, destOutputCell As Range
    Dim srcTotRow As Long, srcTotDescCol As String, srcTotValCol As String
    Dim srcProdDescCell As String
    Dim arrHdgs As Variant
    
    Set destWS = Worksheets("Summary")                      ' Change to your output Sheet name
    arrHdgs = Array("Tab Name", "Product Name", "PIP Cost") ' Change headings as required
    
    Set destOutputCell = destWS.Range("A1")
    With destOutputCell
        .CurrentRegion.ClearContents
        With .Resize(, UBound(arrHdgs) + 1)
            .Value = arrHdgs
            .Font.Bold = True
        End With
    End With
    destNextRow = destWS.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    srcTotDescCol = "D"
    srcTotValCol = "G"
    srcProdDescCell = "C1"
    
    For Each srcWS In Worksheets
        With Application
            srcTotRow = .IfError(.Match("Planned Individual Portion Cost", srcWS.Columns(srcTotDescCol), 0), 0)
            If srcTotRow <> 0 Then
                destWS.Cells(destNextRow, 1).Value = srcWS.Name
                destWS.Cells(destNextRow, 2).Value = srcWS.Range(srcProdDescCell)
                destWS.Cells(destNextRow, 3).Value = srcWS.Cells(srcTotRow, srcTotValCol)
                destNextRow = destNextRow + 1
            End If
        End With
    Next srcWS
    
    destOutputCell.Resize(, UBound(arrHdgs) + 1).EntireColumn.AutoFit
    
End Sub