Find text in Range and return value from offset range

[kamiljaku]

Hello,

I would need to find in Column "C" the strings "Total" and return in column "AB" the values which are in the same row as "Total" but in column "N".

I've tried with loops and offset but this cant be the right approach. Do I need to use the Range.find function here? but how can I combine it with Offset and loop?

I marked with colors on the screen what I want to accomplish. Thank you in advance.

 

[JoeMo]

Maybe:

Sub kamiljaku()
Dim Fnd As Variant, fAdr As String, lr As Long
Set Fnd = Range("C:C").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
Application.ScreenUpdating = False
If Not Fnd Is Nothing Then
    fAdr = Fnd.Address
    Cells(Fnd.Row, "AB").Value = Cells(Fnd.Row, "N").Value
    lr = Fnd.Row + 1
    Do
        Set Fnd = Range("C:C").FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
        Cells(lr, "AB").Value = Cells(Fnd.Row, "N").Value
        lr = Range("AB" & Rows.Count).End(xlUp).Row + 1
    Loop
Else
    MsgBox "No Total found in col C of active sheet"
End If
Application.ScreenUpdating = True
End Sub

 

[kamiljaku]

Maybe:

Sub kamiljaku()
Dim Fnd As Variant, fAdr As String, lr As Long
Set Fnd = Range("C:C").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
Application.ScreenUpdating = False
If Not Fnd Is Nothing Then
    fAdr = Fnd.Address
    Cells(Fnd.Row, "AB").Value = Cells(Fnd.Row, "N").Value
    lr = Fnd.Row + 1
    Do
        Set Fnd = Range("C:C").FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
        Cells(lr, "AB").Value = Cells(Fnd.Row, "N").Value
        lr = Range("AB" & Rows.Count).End(xlUp).Row + 1
    Loop
Else
    MsgBox "No Total found in col C of active sheet"
End If
Application.ScreenUpdating = True
End Sub

Works like a charm. I am trying out to figure out how this method is actually doing the job. Apparently it is under my pay grade

Could you tell me how I would need to change that correctly to gather the output horizontal and not vertical? All my tries are failures.

Thanks in advance

 

[JoeMo]

Works like a charm. I am trying out to figure out how this method is actually doing the job. Apparently it is under my pay grade

Could you tell me how I would need to change that correctly to gather the output horizontal and not vertical? All my tries are failures.

Thanks in advance

Glad its working for you. Try this modification to place the output on a row (starting in column AB) rather than a column.

Sub kamiljaku2()
Dim fnd As Variant, fAdr As String, lr As Long, nxCol As Long
Set fnd = Range("C:C").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
Application.ScreenUpdating = False
If Not fnd Is Nothing Then
    fAdr = fnd.Address
    lr = fnd.Row
    Cells(lr, "AB").Value = Cells(fnd.Row, "N").Value
    Do
        Set fnd = Range("C:C").FindNext(fnd)
        If fnd Is Nothing Then Exit Do
        If fnd.Address = fAdr Then Exit Do
        nxCol = nxCol + 1
        Cells(lr, Columns("AB").Column + nxCol).Value = Cells(fnd.Row, "N").Value
    Loop
Else
    MsgBox "No Total found in col C of active sheet"
End If
Application.ScreenUpdating = True
End Sub