Excel vba string find and replace/change column value with a variable string

[Kaniguan1969]

Hi,

I have a worksheet with autofilter column that i would like to replace or change the string value "Not yet" with "IN TRANSIT". May I ask your help guys on how to do in vba maco. thanks.

below is the macro code.

    
    'replace value string "Not yet" with "IN TRANSIT"
    Dim dat As Variant
    Dim a As Range
    Dim i As Long, FilterRow As Long
    With wsWork
        lrow = .Range("A" & .Rows.count).End(xlUp).Row
        Set FilterRng = .Range("A1:J" & lrow)
        FilterRng.AutoFilter 6, "Not yet"
        FilterRow = .Range("A" & .Rows.count).End(xlUp).Row

        '?replace all string "Not yet" in column F with "IN TRANSIT"
        
    End With

[Kaniguan1969]

Just made this codes and so far it is working.

    With wsWork.Range("F:F")
    .Replace What:="Not yet", Replacement:="IN TRANSIT", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
    End With

btw, May i ask help on how to do this in vba macro. i have a value in column "D" that i would like to removed the delivered string and retain the date format. thanks.

sample

COlumn D
--------------

Delivered 8/7

Delivered 8/7

Delivered 8/7

Delivered 8/10

Delivered 8/10

Delivered 8/12

Delivered 8/12

Delivered 8/12

Delivered 8/12

Delivered 8/14

Delivered 8/14

Delivered 8/14

Result
---------------

8/7/2014

8/7/2014

8/7/2014

8/10/2014

8/10/2014

8/12/2014

8/12/2014

8/12/2014

8/12/2014

8/14/2014

8/14/2014

8/14/2014

[mancubus]

try:

Columns("D").SpecialCells(xlCellTypeConstants, 23).Replace "Delivered ", ""

[Kaniguan1969]

Thanks Mancubus...Btw, its possible when pasting values to working file i dont want to removed the formula of the column F.
also it spoosibel to skip saving when the value of c=5 or C2
.
below is the vba code.

    With wsSource
        LastRow = .Range("B" & .Rows.count).End(xlUp).Row
        Set rng = .Range("B2, E2, D2, H2, C2, J2, H2, E2")
    End With
    
    c = 1   'Start column
    For Each rng In rng.Areas
        rng.Resize(LastRow - 1).Copy
        wsWork.Cells(lrow + 1, c).Offset(1, 0).PasteSpecial Paste:=xlPasteValues ' dont want to remove the formula of destination column.
        c = c + rng.Columns.count
    Next rng

[mancubus]

i'm not sure i understand your requirement correctly.

pls upload a sample file with current column structure and desired column structure.