PDA

View Full Version : Excel vba string find and replace/change column value with a variable string



Kaniguan1969
08-14-2014, 11:07 PM
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
08-14-2014, 11:33 PM
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
08-15-2014, 12:30 AM
try:

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

Kaniguan1969
08-15-2014, 01:25 AM
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
08-15-2014, 01:44 AM
i'm not sure i understand your requirement correctly.

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