Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subject: VBA Question If column contains keyword
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 befo 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subject: VBA Question If column contains keyword
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 -- Regards, Tom Ogilvy "Scott Wagner" wrote: 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 befo 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subject: VBA Question If column contains keyword
Thank you sir!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Sum a column subject to variables in two columns | Excel Worksheet Functions | |||
Two Headers, one subject column and various columns with data | Charts and Charting in Excel | |||
VBA Question If column contains keyword | Excel Programming | |||
Subject: Summing one column where criteria in two other | Excel Worksheet Functions | |||
Formula to identify a keyword in all cells of a column | Excel Discussion (Misc queries) |