PDA

View Full Version : Copy/Paste if cell contains "123" characters



sarora
04-21-2010, 09:20 AM
Hello everyone,

I'm new to VBA and can't seem to find any code to do then following. I'm hoping someone will be able help me out.


In Column "A" the below values are entered, I would like the macro to do the following

1. Search column "A" for cell that contains the characters "123"
2. Copy all contents of cell
3. Paste to next empty cell in column "A"
3. Replace the characters "123" in the pasted cells with value in cell "C1"

EXAMPLE

C1 = 999

Before

PRICINGDATAREPLACE,22.50,123)
PRICINGDATAREPLACE,2.05,456)
PRICINGDATAREPLACE,814.01,642)
PRICINGDATAREPLACE,540.50,123)
PRICINGDATAREPLACE,3.22,957)
PRICINGDATAREPLACE,705.50,324)
PRICINGDATAREPLACE,456.50,123)

After
PRICINGDATAREPLACE,22.50,123)
PRICINGDATAREPLACE,2.05,456)
PRICINGDATAREPLACE,814.01,642)
PRICINGDATAREPLACE,540.50,123)
PRICINGDATAREPLACE,3.22,957)
PRICINGDATAREPLACE,705.50,324)
PRICINGDATAREPLACE,456.50,123)
PRICINGDATAREPLACE,22.50,999)
PRICINGDATAREPLACE,540.50,999)
PRICINGDATAREPLACE,456.50,999)
Thanks in advance for all your help!!

p45cal
04-21-2010, 09:35 AM
Run this macro with that sheet active:
Sub blah()
lastrow = Range("A1").End(xlDown).Row
For Each cll In Range("A1:A" & lastrow).Cells
If InStr(cll.Value, "123") > 0 Then
lastrow = lastrow + 1
Cells(lastrow, 1).Value = Replace(cll.Value, "123", Range("C1").Value)
End If
Next cll
End Sub

mdmackillop
04-21-2010, 11:04 AM
Cells(lastrow, 1).Value = Replace(cll.Value, "123", Range("C1").Value)
Problem if 123 appears twice in the cell.:thumb

p45cal
04-21-2010, 11:29 AM
Problem if 123 appears twice in the cell.:thumb
Not if that's what he wants! I just stuck to the spec. :devil2:
I just hope he doesn't want all the highlighting too..