PDA

View Full Version : Row Search & paste string



vzachin
02-11-2008, 10:11 AM
hi,

i have data beginning in row5, from column X to possibly column Q. what i need is to find a few strings (possibly 5 strings) in each row. and if the string is found, place the found string into the corresponding column Y. If the string appears in more than 1 column, return "No Good" into column Y. If the string is not found, then leave the column blank.


i have the following so far, finding last row & column. i recorded a macro to find "RED" and place the value into Column Y but i don't know how to find additional strings.

currently i'm looking for 3 strings "RED","YELLOW", "PURPLE".

Sub Test1()
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
iLastCol = xlLastCol
iLastRow = Cells(Rows.Count, "X").End(xlUp).Row

Range("AA5:BQ5").Select
Selection.Find(What:="RED", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub


thanks
zach