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.
<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
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