VBA Question If column contains keyword

S

Scott Wagner

This is simple item for the gurus I am sure.

In my worksheet, in column C I want to identify a specific item from a list
of many others (by keyword) and then move the contents of another cell
(target).

Keyword in column C = "TVSS"

Target cell = one cell below and one cell to the right.
(Example: If TVSS is in cell C3, the target cell is D4)

Move target to = one cell up from orginal location.
(Example: If target cell was D4, move to D3)

Here is a before:
ColA ColB ColC ColD
1 1 Wall Mounted TVSS(AC11)
TME120Y100WM
2 1 Wall Mounted TVSS(AC11)
TME160Y100WM
3 1 Wall Mounted TVSS(AC11)
TME180Y100WM

Here is what I want after:
ColA ColB ColC ColD
1 1 Wall Mounted TVSS(AC11) TME120Y100WM
2 1 Wall Mounted TVSS(AC11) TME160Y100WM
3 1 Wall Mounted TVSS(AC11) TME180Y100WM


Thanks in advance!

Scott
 
B

Bob Phillips

Set cell = Columns(1).Find(What:="TVSS",Lookat:=xlPart)
If Not cell Is Nothing Then
cell.Offset(0,1).Value = cell.Offset(1,1).Value
Row(cell.Offset(1,0).Row).Delete
End if

If you don't want the row delete, just remove that line.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

Scott Wagner

Bob,

Thanks for the quick response.

The code isn't working for me. I think it is because in my worksheet the
columns have some cells with blanks intermingled. The code you offered looks
like it stops if cell is nothing???

You know much more about this than I.

Thanks,

Scott
 
B

Bob Phillips

Scott,

Can you give a more accurate data example. Separate cells with a pipe
character, like so

123 | abc

so that we can see it better.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

Scott Wagner

Sorry about that sir. This should better illustrate.

Here is a before:
ColA | ColB | ColC | ColD
1 | 1 | Wall Mounted TVSS(AC11) |
| | | TME120Y100WM
2 | 1 | Wall Mounted TVSS(AC11) |
| | | TME160Y100WM
3 | 1 | Wall Mounted TVSS(AC11) |
| | | TME180Y100WM

Here is what I want after:
ColA | ColB | ColC | ColD
1 | 1 | Wall Mounted TVSS(AC11) | TME120Y100WM
2 | 1 | Wall Mounted TVSS(AC11) | TME160Y100WM
3 | 1 | Wall Mounted TVSS(AC11) | TME180Y100WM
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top