Subject: VBA Question If column contains keyword

[Scott Wagner]

This is a topic that was posted earlier in the week, that I'm making an
attempt to explain more clearly. I apologize for the confusion, and hope one
of the guru's can assist with.

In my worksheet I have a list of products, which is a mixture of products
that are a "one item" part number only. Also, there are products that are
custom built and consist of components.

I've dealt with both situations seperately and so far have worked out how to
deal with each with lots of help from people on this board. There are a few
exceptions that come up that are causing headaches. These cases are for very
specific products, and because of this can be identified by keywords in the
description.

Here is a logical breakdown:

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

Col A = Line item #
Col B = Quantity on order
Col C = Item description, keyword = "TVSS"
Col D = Part number

The problem is that the part number for the TVSS item is one line below the
TVSS line. When the keyword is found I want to move the contents from the
traget cell.

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

 

[Tom Ogilvy]

Sub FINDTVSS()
Dim rng As Range
Dim rng1 As Range
Dim lngF As Long
Set rng = Columns(3).Find(What:="TVSS", _
After:=Range("C1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
lngF = rng.Row
Do
rng.Offset(0, 1).Value = rng.Offset(1, 1).Value
rng.Offset(1, 0).EntireRow.Delete
Set rng = Columns(3).FindNext(rng)
Loop While rng.Row > lngF
End If
End Sub

 

[Scott Wagner]

Thank you sir!