VBA Question If column contains keyword

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
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)
 
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
 
Back
Top