Excel VB Search function not finding a number

I am using a search function to compare data to see if an identifier already exists 开发者_Python百科in a work sheet but if its a number it doesn't find it via VB Script. If I find it manually it finds it fine.. this is mind boggling.

The following code is in a loop that goes over each SKU ID in one sheet to see if it exists in another sheet. (If there is a better way to do this let me know.) If the sku is not found it adds it to the sheet, I always come up with duplicate data because it doesn't find it via vb script even though it already exists... Let me know if this doesn't make sense, I'll try to reiterate. a sku is always unique

Sheets(productsheet).Select
        On Error Resume Next
        Cells.Find(What:=sku, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

        If Err.Number <> 0 Then
            newSKU = newSKU + 1
            Range("A" & CurrentRow).Select
            ActiveCell.Value = trimmedSku
            ActiveCell.Font.Color = red
            With Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlTop
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With

            Range("B" & CurrentRow).Select
            ActiveCell.Value = skudescription
            With Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlTop
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With

            Range("D" & CurrentRow).Select
            ActiveCell.Value = sku
            With Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlTop
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With

            Range("I" & CurrentRow).Select
            ActiveCell.Value = 1

            Range("K" & CurrentRow).Select
            ActiveCell.Value = cost

            Range("L" & CurrentRow).Select
            ActiveCell.Value = price

            Range("M" & CurrentRow).Select
            ActiveCell.Value = price

            Range("O" & CurrentRow).Select
            ActiveCell.Value = "Y"

            Range("P" & CurrentRow).Select
            ActiveCell.Value = "N"

            Range("Q" & CurrentRow).Select
            ActiveCell.Value = "Y"

            Range("AI" & CurrentRow).Select
            ActiveCell.Value = 1

            CurrentRow = CurrentRow + 1
        Else
            'Skip because it was found
        End If  

---

There's no need to select or activate anything to do what you want. You may not be finding the value because of some of your find arguments, but I would first rewrite the code to avoid changing sheets. Something like

Dim sh As Worksheet
Dim rFound As Range

Set sh = ThisWorkbook.Sheets("productsheet")

Set rFound = sh.Cells.Find(sku, , xlValues, xlPart)

If rFound Is Nothing Then
    Set rNext = sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1, 0)
    rNext.Value = trimmedsku
    rNext.Offset(0, 1).Value = skudescription
    rNext.Offset(0, 3).Value = sku
    rNext.Offset(0, 8).Value = 1
    rNext.Offset(0, 10).Value = cost
    rNext.Offset(0, 11).Value = price
    rNext.Offset(0, 12).Value = price
    rNext.Offset(0, 14).Value = "Y"
    rNext.Offset(0, 15).Value = "N"
    rNext.Offset(0, 16).Value = "Y"
    rNext.Offset(0, 34).Value = 1
End If

If rFound is coming up Nothing (sku not found) and it shouldn't be, then make sure the arguments of Find are correct. Do you want to look at xlFormulas or xlValues? Whole or Part?

I don't see anything in your Find arguments that would make it more restrictive, so I can't give you any specific advice on which arguments to change. Maybe you could make a new test procedure similar to what I've posted and see if not changing sheets help.