Using FIND Function, to Find the last value in a Column, in a different sheet.

[johnmpc]

I have this code, WHICH DOESN'T WORK.

What i am doing, i enter a value in a new row in column C of sheet ("PET MILL") I want to use the find function to return the description of this reference which is in Column B of sheet("PETE REFS")

I am having a problem with the code looking for the last cell in column 3.
How do i make this work, please?

Also, What i plan to do after getting this to work is have an INPUT box that asks to input the description and add it and the PETE REF to the last row in the PETE REFS Sheet.

Any help greatly appreciated. Thanks

Sub findPETEREF()

 Dim c As Range
    Dim PETEREF As Range
    Set PETEREF = Sheets("PETE REFS").Range("A:A").Find(What:=Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Row.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not PETEREF Is Nothing Then
    Range(Rows.Count, 3).End(xlUp).Value = PETEREF.Offset(, 4).Value
    Else
    MsgBox "PETE REF NOT FOUND"
    End If
    





End Sub

 

[Fluff]

Maybe

Sub findPETEREF()

 Dim c As Range
    Dim PETEREF As Range
    Set PETEREF = Sheets("PETE REFS").Range("C:C").Find(Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Value, , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)
    If Not PETEREF Is Nothing Then
    Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Offset(, -1).Value = PETEREF.Offset(, -1).Value
    Else
    MsgBox "PETE REF NOT FOUND"
    End If
    





End Sub

 

[johnmpc]

Maybe

Sub findPETEREF()

 Dim c As Range
    Dim PETEREF As Range
    Set PETEREF = Sheets("PETE REFS").Range("C:C").Find(Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Value, , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)
    If Not PETEREF Is Nothing Then
    Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Offset(, -1).Value = PETEREF.Offset(, -1).Value
    Else
    MsgBox "PETE REF NOT FOUND"
    End If
   





End Sub

THanks for the reply.

Not quite right. Maybe misinterpretation of my description.

Sheet 1 is PET MILL, Last cell in Column C is the value i want to FIND. It is looking in Column A of sheet 2 which is PETE REFS

The description i want to return is in column B of sheet 2 PETE REFS. i want this placed in the same row as the look up value (PET MILL) but in Column G .

 

[Fluff]

How about

Sub findPETEREF()

 Dim c As Range
    Dim PETEREF As Range
    Set PETEREF = Sheets("PETE REFS").Range("A:A").Find(Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Value, , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)
    If Not PETEREF Is Nothing Then
    Sheets("PET MILL").Range(Rows.Count, 3).End(xlUp).Offset(, 4).Value = PETEREF.Offset(, 1).Value
    Else
    MsgBox "PETE REF NOT FOUND"
    End If
    





End Sub

 

[johnmpc]

I've currently got it running off a button, when i run it pops up 400 error.

When i go through Debug it comes up with run time error 1004 on the SET line

 

[Fluff]

What is the actual error?

 

[johnmpc]

I've currently got it running off a button, when i run it pops up 400 error.

When i go through Debug it comes up with run time error 1004 on the SET line

Application defined or object defined error

 

[Fluff]

Ok, missed that you were using Range & not Cells, it should be

    Set PETEREF = Sheets("PETE REFS").Range("A:A").Find(Sheets("PET MILL").Cells(Rows.Count, 3).End(xlUp).Value, , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)

 

[johnmpc]

Thank you. This works perfectly.

Any chance you would be able to assist in writing code for an input box instead of a message box please.
I would like it to add the new line to the PETE REFS sheet.

 

[Fluff]

As that is a substantially question, it needs a new thread. Thanks