Search IF row of data (multiple cells) contains specific text

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro. Any help would be greatly appreciated.

BOL </SPAN></SPAN>


FinPro</SPAN></SPAN>

Sup</SPAN></SPAN>
Veh</SPAN></SPAN>
1Dn</SPAN></SPAN>
2Cn</SPAN></SPAN>
2Dn</SPAN></SPAN>
Bio</SPAN></SPAN>
Injec</SPAN></SPAN>
1010037</SPAN></SPAN>
03/01/13 04:19:49</SPAN></SPAN>
03/01/13 04:39:41</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>

Contract</SPAN></SPAN>
3</SPAN></SPAN>
650</SPAN></SPAN>
0</SPAN></SPAN>
5,375</SPAN></SPAN>
475</SPAN></SPAN>
6.5030</SPAN></SPAN>
1010038</SPAN></SPAN>
03/01/13 05:19:44</SPAN></SPAN>
03/01/13 05:29:40</SPAN></SPAN>
Optimum Cold Weather Clear B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
0</SPAN></SPAN>
648</SPAN></SPAN>
0</SPAN></SPAN>
51</SPAN></SPAN>
0.7180</SPAN></SPAN>
1010039</SPAN></SPAN>
03/01/13 05:31:58</SPAN></SPAN>
03/01/13 05:37:32</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
150</SPAN></SPAN>
0</SPAN></SPAN>
1,241</SPAN></SPAN>
109</SPAN></SPAN>
1.5160</SPAN></SPAN>
1010040</SPAN></SPAN>
03/01/13 05:34:36</SPAN></SPAN>
03/01/13 05:45:04</SPAN></SPAN>
Optimum Cold Weather Clear B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
0</SPAN></SPAN>
2,316</SPAN></SPAN>
0</SPAN></SPAN>
183</SPAN></SPAN>
2.5130</SPAN></SPAN>
1010041</SPAN></SPAN>
03/01/13 05:37:45</SPAN></SPAN>
03/01/13 05:41:48</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Contract</SPAN></SPAN>
100</SPAN></SPAN>
0</SPAN></SPAN>
827</SPAN></SPAN>
73</SPAN></SPAN>
0.9980</SPAN></SPAN>
1010042</SPAN></SPAN>
03/01/13 05:45:54</SPAN></SPAN>
03/01/13 06:05:50</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Contract</SPAN></SPAN>
3</SPAN></SPAN>
650</SPAN></SPAN>
0</SPAN></SPAN>
5,375</SPAN></SPAN>
475</SPAN></SPAN>
6.5030</SPAN></SPAN>
1010043</SPAN></SPAN>
03/01/13 05:49:22</SPAN></SPAN>
03/01/13 06:02:03</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
350</SPAN></SPAN>
0</SPAN></SPAN>
2,895</SPAN></SPAN>
255</SPAN></SPAN>
3.5100</SPAN></SPAN>
BOL </SPAN></SPAN>


FinPro</SPAN></SPAN>

Sup</SPAN></SPAN>
Veh</SPAN></SPAN>
1Dn</SPAN></SPAN>
2Cn</SPAN></SPAN>
2Dn</SPAN></SPAN>
Bio</SPAN></SPAN>
Injec</SPAN></SPAN>
1010038</SPAN></SPAN>
03/01/13 05:19:44</SPAN></SPAN>
03/01/13 05:29:40</SPAN></SPAN>
Optimum Cold Weather Clear B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
0</SPAN></SPAN>
648</SPAN></SPAN>
0</SPAN></SPAN>
51</SPAN></SPAN>
0.7180</SPAN></SPAN>
1010040</SPAN></SPAN>
03/01/13 05:34:36</SPAN></SPAN>
03/01/13 05:45:04</SPAN></SPAN>
Optimum Cold Weather Clear B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
0</SPAN></SPAN>
2,316</SPAN></SPAN>
0</SPAN></SPAN>
183</SPAN></SPAN>
2.5130</SPAN></SPAN>
1010037</SPAN></SPAN>
03/01/13 04:19:49</SPAN></SPAN>
03/01/13 04:39:41</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>

Contract</SPAN></SPAN>
3</SPAN></SPAN>
650</SPAN></SPAN>
0</SPAN></SPAN>
5,375</SPAN></SPAN>
475</SPAN></SPAN>
6.5030</SPAN></SPAN>
1010039</SPAN></SPAN>
03/01/13 05:31:58</SPAN></SPAN>
03/01/13 05:37:32</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
150</SPAN></SPAN>
0</SPAN></SPAN>
1,241</SPAN></SPAN>
109</SPAN></SPAN>
1.5160</SPAN></SPAN>
1010041</SPAN></SPAN>
03/01/13 05:37:45</SPAN></SPAN>
03/01/13 05:41:48</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Contract</SPAN></SPAN>
100</SPAN></SPAN>
0</SPAN></SPAN>
827</SPAN></SPAN>
73</SPAN></SPAN>
0.9980</SPAN></SPAN>
1010042</SPAN></SPAN>
03/01/13 05:45:54</SPAN></SPAN>
03/01/13 06:05:50</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Contract</SPAN></SPAN>
3</SPAN></SPAN>
650</SPAN></SPAN>
0</SPAN></SPAN>
5,375</SPAN></SPAN>
475</SPAN></SPAN>
6.5030</SPAN></SPAN>
1010043</SPAN></SPAN>
03/01/13 05:49:22</SPAN></SPAN>
03/01/13 06:02:03</SPAN></SPAN>
Optimum Cold Weather Dyed B5%</SPAN></SPAN>
Spot</SPAN></SPAN>
350</SPAN></SPAN>
0</SPAN></SPAN>
2,895</SPAN></SPAN>
255</SPAN></SPAN>
3.5100</SPAN></SPAN>

<TBODY>
</TBODY>


'Finished Product
LastRowData = RowData - 1
Range("A1:Z100").Select
Cells.Find(What:="Finished Product").Select
FPCol = Selection.Column
FPRow = Selection.Row

'Sort data by Clear vs. Dyed, THEN Supplier, THEN BOL#
Sheets("Data").Select
Range("A3", Cells(LastRowData, "Z")).Select
Sheets("Data").Sort.SortFields.Clear
Sheets("Data").Sort.SortFields.Add Key:=Range(Cells(3, FPCol), Cells(LastRowData, FPCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Sheets("Data").Sort.SortFields.Add Key:=Range(Cells(3, 1), Cells(LastRowData, 1)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A3", Cells(LastRowData, "Z"))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hello John
This macro will create the new column you talked about. Maybe you'll have to adjust the column references.

Code:
Sub AuxCol()


' FinPro data at column L
Columns("m:m").Insert shift:=xlToLeft
Columns("m:m").Insert shift:=xlToLeft
Range("m3").FormulaR1C1 = "=if(find(""Dyed"",rc[-1],1)>0,""Dyed"",0)"
Range("n3").FormulaR1C1 = "=if(iserr(rc[-1]),if(find(""Clear"",rc[-2],1)>0,""Clear"",0),rc[-1])"
Range("m3:n3").AutoFill Destination:=Range("m3:n" & LastRow(ThisWorkbook.Name, _
ActiveSheet.Name)), Type:=xlFillDefault
Columns(13).Hidden = True


End Sub


Public Function LastRow(wname$, which$) As Long
    Workbooks(wname).Sheets(which).Activate
    If WorksheetFunction.CountA(Cells) = 0 Then
        LastRow = 0
        Exit Function
    End If
    LastRow = Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
End Function
 
Upvote 0
Worf,

I haven't had a chance to implement your code yet as I have been pulled away on another project. But your reply is greatly appreciated and I will give you feedback once I get a chance to implement it.

John
 
Upvote 0
Worf,

I updated your sub to my column references and "unhid" column 13 (or my equivalent) so that I can follow the process of the macro to learn. However, all of my excel experience is through experiment and picking things up from forums, so the following may be a trivial question: I do not understand how the the function LastRow works. I have personally never used/written a user defined function. I moved the LastRow function to the end of my macro, as otherwise it inerupts my Sub. Could you possible walk through function for me? Else a good reference I can read to break it down would also be appreciated.

Lastly, it looks as if I am reaching the point that my vba capabilities and being limited by my lack of basic instruction. Do you have any suggestions for a good book/website where I can 'learn the basics' without being overly annoyed that I have already learned more complicated functions?

' FinPro data at column D
Range("AA3").FormulaR1C1 = "=if(find(""Dyed"",rc[-20],1)>0,""Dyed"",0)"
Range("AB3").FormulaR1C1 = "=if(iserr(rc[-1]),if(find(""Clear"",rc[-21],1)>0,""Clear"",0),rc[-1])"
Range("AA3:AB3").AutoFill Destination:=Range("AA3:AB" & LastRow(ThisWorkbook.Name, _
ActiveSheet.Name)), Type:=xlFillDefault
'Columns(13).Hidden = True

Thanks again for all of your help,
Sevy
 
Upvote 0
Hello
The LastRow function is intended to be a little black box that returns the last used row at sheet “which” of workbook “wname”. If you want to understand it more, search Excel Help for “CountA” and “Find”.
Buying a book is an excellent idea; they present the subject in an organized way. There are many expert authors around, starting with Bill Jelen, owner of this site, but I can only recommend what I have already read, both by John Walkenbach:
- Excel Bible – meant for users of all levels, covers almost all Excel features and includes an introduction to VBA
- Excel Power Programming with VBA – The preface states that “this is not a book for beginning Excel users”, but you should not feel intimidated…
- No book will contain every conceivable topic on a subject, so when this happens, it’s time to use Google. The Internet is a great research tool.
- I updated your column references, in case FinPro is at column D.
- If you need further help with the rest of the project, feel free to ask.

Code:
Sub Sevy()


' FinPro data at column D
Range("AA3").FormulaR1C1 = "=if(find(""Dyed"",rc[-23],1)>0,""Dyed"",0)"
Range("AB3").FormulaR1C1 = "=if(iserr(rc[-1]),if(find(""Clear"",rc[-24],1)>0,""Clear"",0),rc[-1])"
Range("AA3:AB3").AutoFill Destination:=Range("AA3:AB" & LastRow(ThisWorkbook.Name, _
ActiveSheet.Name)), Type:=xlFillDefault


End Sub
 
Upvote 0
Worf,

Thank you very much for all of your help. Over the past week I have learned quite a bit through forums and experimentation and I understand your code now. I am still working on the same project, but hopefully will complete it early this week (my deadline was April 1st to start the inventory on the new month!). I'll post new threads if I need any more help.

Thanks again,
John
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top