VBA: If Range Contains Cell Value Copy than del

[harky]

I need help


If Col C cell contain 'Poster' OR 'Index' COPY TO
Col E Cell to Col A Cell Than
Delete Col E E

the flow is

*xxx refer to some text

If Col C cell contain 'Poster' OR 'Index'
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]Poster[/TD]
[TD]13[/TD]
[TD]62131[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]Index[/TD]
[TD]16[/TD]
[TD]62141[/TD]
[/TR]
</tbody>[/TABLE]


COPY TO Col E Cell to Col A Cell Than
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]62131[/TD]
[TD]xxx[/TD]
[TD]Poster[/TD]
[TD]13[/TD]
[TD]62131[/TD]
[/TR]
[TR]
[TD]62141[/TD]
[TD]xxx[/TD]
[TD]Index[/TD]
[TD]16[/TD]
[TD]62141[/TD]
[/TR]
</tbody>[/TABLE]


Delete Col E
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]62131[/TD]
[TD]xxx[/TD]
[TD]Poster[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62141[/TD]
[TD]xxx[/TD]
[TD]Index[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[harky]

Hi, sorry took a while to reply.
I just tested. And now it work well
Thanks!

Still not very clear but I notice that you have not made the red & blue changes that I suggested in post 5. That would be a good place to start.

 

[Peter_SSs]

I just tested. And now it work well
Thanks!

You're welcome. Thanks for letting us know.

 

[harky]

Hi,

I spotted one issue.

The code work well but i notice it fill down tlll the very last row of the sheet 10000xxx (I only tested with 24row data)
which make the file size big.
Can it just code on the active-cell?

You're welcome. Thanks for letting us know.

 

[harky]

24row data - saved file is 200kb

but after tht code, the saved file become 10mb
And Col A & Col E had #NA ended at row 104576

Hi,

I spotted one issue.

The code work well but i notice it fill down tlll the very last row of the sheet 10000xxx (I only tested with 24row data)
which make the file size big.
Can it just code on the active-cell?

 

[Peter_SSs]

Hi,

I spotted one issue.

The code work well but i notice it fill down tlll the very last row of the sheet 10000xxx

I think that you have still not implemented everything that I told you in post 5

If you want to operate on sheet "path" I would add the blue bits and remove the red bits. Also, always best to declare (Dim) your variables.

Sub Poster_Index()
  Dim ws As Worksheet
  
  Set ws = Sheets("path")
  With ws.Range("A1:E" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)
    <del>ws</del>.Columns(1).Value = Evaluate(Replace(Replace(Replace("IF(ISNUMBER(SEARCH(""|""&#C&""|"",""|Poster|Index|"")),#E   ,#A   )", "#C", .Columns(3).Address), "#E   ", .Columns(5).Address), "#A   ", .Columns(1).Address))
    <del>ws</del>.Columns(5).Value = Evaluate(Replace(Replace("IF(ISNUMBER(SEARCH(""|""&#C&""|"",""|Poster|Index|"")),"""",#E   )", "#C", .Columns(3).Address), "#E   ", .Columns(5).Address))
  End With
End Sub

If you then still have problems, please post the actual code that you are using using.

 

[harky]

Hi.. sorry. I notice, i accidentally remove the dot beside Columns
it working great now

I think that you have still not implemented everything that I told you in post 5




If you then still have problems, please post the actual code that you are using using.

 

[Peter_SSs]

Hi.. sorry. I notice, i accidentally remove the dot beside Columns
it working great now

OK, that certainly could cause the same problem as not removing the 'ws'
Glad it is working now.

 

[harky]

Hi, i notice the coding clear the format which i had original.

Some of my number had 0 in-front (e.g 012345) but the code removed the 0 somehow.
I need to retain the 0 in front

OK, that certainly could cause the same problem as not removing the 'ws'
Glad it is working now.

 

[harky]

i need to retain the original format value which i had..

the code somehow replace with new value.

OK, that certainly could cause the same problem as not removing the 'ws'
Glad it is working now.

 

[harky]

If Column C cell contain 'Poster' or 'Index'

CUT & PASTE Col E to Col A