If Cell contains specific text in col A copy cell from Col C into Col F stacked

Question

I am working on code where if in Column "A"="test" then to copy cell from Col C into Col F but without spaces . Can someone please help?

Sub FindValuePaste()

    Dim FndRng As Range
    Dim cll As Range

    Set FndRng = Range("A3:A9")

    For Each cll In FndRng
        If cll.Value = "test" Then
           ' cll.Offset(0, 5) = cll.Offset(0, 2).Value
            Range("F1").Offset(1) = cll.Offset(0, 2).Value
        End If
    Next cll

End sub

Appreciate your help and thank you in advance!

Answer 1

• Use .End(xlUp).Row to locate the last row

Sub FindValuePaste()
    Dim FndRng As Range
    Dim cll As Range
    Dim lastRow as Long, oSht as Worksheet
    Set oSht = ActiveSheet ' modify as needed
    lastRow = oSht.Cells(oSht.Rows.Count, "F").End(xlUp).Row 
    If len(oSht.Range("F" & lastRow)) > 0 Then  lastRow = lastRow + 1
    Set FndRng = oSht.Range("A3:A9")

    For Each cll In FndRng
        If cll.Value = "test" Then
           ' cll.Offset(0, 5) = cll.Offset(0, 2).Value
            oSht.Range("F" & lastRow).Value = cll.Offset(0, 2).Value
            lastRow = lastRow + 1            
        End If
    Next cll
End sub

---

• Use CountA to get the count of non-blank cells (The data should be start from F1 on Col F)

Sub FindValuePaste2()

    Dim FndRng As Range
    Dim cll As Range
    Dim lastRow As Long, oSht As Worksheet
    Set oSht = ActiveSheet ' modify as needed
    lastRow = Application.CountA(oSht.Columns("F")) + 1
    Set FndRng = oSht.Range("A3:A9")
    For Each cll In FndRng
        If cll.Value = "test" Then
           ' cll.Offset(0, 5) = cll.Offset(0, 2).Value
            oSht.Range("F" & lastRow) = cll.Offset(0, 2).Value
            lastRow = lastRow + 1
        End If
    Next cll

End Sub

Answer 2

Copy Matching Row From Another Column

A Quick Fix

Sub FindValuePaste()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim srg As Range: Set srg = ws.Range("A3:A9")
    Dim dcell As Range: Set dcell = ws.Range("F3")
    
    Dim scell As Range
    
    For Each scell In srg.Cells
        If StrComp(CStr(scell.Value), "test", vbTextCompare) = 0 Then
            dcell.Value = scell.Offset(, 2).Value
            Set dcell = dcell.Offset(1)
        End If
    Next scell
    
End Sub

Answer 3

What about a formula in cell F3:

=SUBSTITUTE(INDEX(FILTER(A3:C9,A3:A9="test"),0,3)," ","")

• Where the FILTER function filters the table A3:C9 to only rows with 'test'
• Where the INDEX function picks column number 3 from that filtered range
• Where SUBSTITUTE replaces the spaces (although I did not see the necessity in the example sheet)