I should have saw that! Thanks, man.
--
Cheers,
Ryan
"Joel" wrote:
> 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" wrote:
>
> > 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
> >
> > --
> > Cheers,
> > Ryan
|
Similar Threads
