VBA code to find value in once sheet and paste it in another sheet

[Tanyaann1995]

Hi,

In sheet 2, when I write a part number in column E24, I want the code to find that part in Sheet 3. Please see attached pictures of how both the sheets look like.
The below is my code but an error message "Application or object defined error" keeps popping up after the first For loop. Pls check and advise.

Sub Price()

Dim pno As Double
Dim LastRow As Long
Dim i As Integer
Dim LastRowinMainSheet As Long
Dim j As Integer

LastRowinMainSheet = Cells.Find(What:="*", _
                    After:=Range("E23"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

LastRow = Worksheets(3).UsedRange.SpecialCells(x1CellTypeLastCell).Row

For j = 24 To LastRowinMainSheet
Worksheets(2).Cells(j, 5).Value = pno

For i = 3 To LastRow

If Worksheets(3).Cells(i, 2).Value = pno Then
Worksheets(3).Cells(i, 3).Copy
Worksheets(2).Cells(j, 4).PasteSpecial xlPasteValues
Worksheets(3).Cells(i, 4).Copy
Worksheets(2).Cells(j, 6).PasteSpecial xlPasteValues
Worksheets(3).Cells(i, 5).Copy
Worksheets(2).Cells(j, 7).PasteSpecial xlPasteValues
Worksheets(3).Cells(i, 14).Copy
Worksheets(2).Cells(j, 12).PasteSpecial xlPasteValues
End If

Next i

Next j


End Sub

 

[Tanyaann1995]

What line is highlighted when you click debug?

When clicking Debug, the last line that gets highlighted is pno = Worksheets(2).Cells(j, 5).Value
After this, the above error message pops up.

 

[Fluff]

Ok, try

pno = ThisWorkbook.Worksheets(2).Cells(j, 5).Value

 

[Tanyaann1995]

The above code worked . Thank you

 

[Tanyaann1995]

Ok, try

pno = ThisWorkbook.Worksheets(2).Cells(j, 5).Value

Thanks for the above code. Currently, this code is taking too long since it checks if the value is equal to every other value in the other workbook. Please advise if there a method to just find that value instead of using If statement to check if that value is equal to every other value. The below is the current code.

For j = 24 To LastRowinMainSheet
pno = ThisWorkbook.Worksheets(2).Cells(j, 5).Value

For i = 3 To LastRow

If f.Worksheets(1).Cells(i, 2).Value = pno Then
ThisWorkbook.Worksheets(2).Cells(j, 4).Value = f.Worksheets(1).Cells(i, 3).Value
ThisWorkbook.Worksheets(2).Cells(j, 6).Value = f.Worksheets(1).Cells(i, 4).Value
ThisWorkbook.Worksheets(2).Cells(j, 7).Value = f.Worksheets(1).Cells(i, 5).Value
ThisWorkbook.Worksheets(2).Cells(j, 12).Value = f.Worksheets(1).Cells(i, 14).Value

End If

Next i

Next j

After finding the value, I will still need to copy the corresponding part numbers, description and price and paste them in the other workbook similar to above code.

 

[Fluff]

You can use Range.Find to find the value rather than looping. But as that's a totally different question, you will need to start a new thread if you need help with it.