Data Not Transferring From Source to Data Worksheet

[RyanH]

I have two worksheets: Source Worksheet is Sheets("New Data") and Global
Worksheet is Sheets("Global Schedule"). The macro below scans down "New
Data" Col.A and trys to find the sales order number in "Global Schedule"
Col.A. If the sales order # is not in "Global Schedule" it copies the data
from "New Data" to "Global Schedule"

This is my problem. For some reason when the macro trys to find sales order
# "19839-1" in "Global Schedule" it finds and returns "19839-10", why? Yes
"19839-10" is there, but it is not "19839-1", thus "19839-1" does not copy
from "New Data" to "Global Schedule". Any ideas?

Private Sub CopyNewItemsToGlobal()

SubName = "CopyNewItemsToGlobal"

Dim lngLastRow As Long
Dim rngNewData As Range
Dim rngGlobalRange As Range
Dim lngInsertRow As Long
Dim cell As Range
Dim rngFoundData As Range

' set Crystal data to find
With Sheets("New Data")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rngNewData = .Range("A1:A" & lngLastRow)
End With

With Sheets("Global Schedule")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rngGlobalRange = .Range("A3:A" & lngLastRow)
.Activate
.Unprotect "AdTech"
End With

' insertion row is the last row of global schedule
lngInsertRow = lngLastRow + 1

With Application
.ScreenUpdating = True
.StatusBar = "Finding New Data and Applying to Schedule...Please Wait"
.Cursor = xlNorthwestArrow
End With

' select lastrow so user can see items importing into schedule
Sheets("Global Schedule").Cells(lngLastRow, "A").Select

' copy data from new data sheet to global schedule sheet
For Each cell In rngNewData
Set rngFoundData = rngGlobalRange.Find(What:=cell.Text,
LookIn:=xlValues)

' if crystal data is not in global and isn't red, copy new data to
global
If rngFoundData Is Nothing And cell.Font.ColorIndex <> 3 Then

' copy new data to global
Sheets("Global Schedule").Range("A" & lngInsertRow & ":Q" &
lngInsertRow).Value = _
Sheets("New Data").Range("A" & cell.Row & ":Q" &
cell.Row).Value

' reset the global range because you added a line to it
Set rngGlobalRange = Sheets("Global Schedule").Range("A3:A" &
lngInsertRow)
lngInsertRow = lngInsertRow + 1

End If
Next cell

End Sub

 

[Joel]

In th efind you need to use the lookat property

from
Set rngFoundData = rngGlobalRange.Find(What:=cell.Text,
LookIn:=xlValues)

to
Set rngFoundData = rngGlobalRange.Find(What:=cell.Text,
LookIn:=xlValues,lookat:=xlwhole)

 

[RyanH]

I should have saw that! Thanks, man.