Rob, thanks for the tip. Gave me an idea and it seems to have solved my
problem. The entire formatting macro completes in under 2 minutes. The
improvements are significant as it using my previous solutions I never
actually completed processing.

Here's the solution (code). I can probably find a few improvements, but
it's operational in it's current state.

Public Sub GetCommodityGroup()
On Error GoTo Err_GetCommodityGroup
Dim wrkbk As Workbook
Dim rowNum As Long ' row number
Dim currentPart As String, currentCommodityGroup As String

Call SortFormattedByPart

' set status bar text to indicate process
Application.StatusBar = "Opening commodity group file " &
CG_WORKBOOK_FILE & "..."
Set wrkbk = Workbooks.Open(CG_WORKBOOK_PATH & CG_WORKBOOK_FILE, 0, True)

rowNum = 2

' loop through the rows of the formatted sheet
Do While ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum ,
FE_PART_COLNUM).Value < ""

' if this is a new part number
If currentPart <
ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value Then
currentPart =
ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value
currentCommodityGroup = ""

' set status bar text to indicate process
Application.StatusBar = "Retrieving commodity group for part " &
currentPart & "..."

With wrkbk.Worksheets(CG_SHEETNAME).Cells
Set c = .Find(currentPart, LookIn:=xlValues)
If Not c Is Nothing Then
Do
If TrimCINCOM(c.Value) = currentPart Then
currentCommodityGroup =
Trim(wrkbk.Worksheets(CG_SHEETNAME).Cells(c.Row,
CG_COMMODITYGROUP_COLNUM).Value)
Exit Do
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

End If

ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum ,
FE_COMMODITYGROUP_COLNUM).Value = currentCommodityGroup

' increase row counter
rowNum = rowNum + 1
Loop

Exit_GetCommodityGroup:

' clear status bar text
Application.StatusBar = ""

wrkbk.Close False ' close workbook
Set wrkbk = Nothing ' free memory

Exit Sub

Err_GetCommodityGroup:
MsgBox Err.Number & Chr(10) & Err.Description
Resume Exit_GetCommodityGroup

End Sub

--
Disregard, this is so I can find my post later.
***postedbyJay***


"Rob Edwards" wrote:

You can always use excels FIND function.

#
Selection.Find(What:=vSourcePart, After:=ActiveCell, LookIn:=xlFormulas
_
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
#

vSourcePart is the item you are trying to find. Use error handling in
case nothing is found.

Rob Edwards

Always look on the bright side of life!

*** Sent via Developersdex http://www.developersdex.com ***