Tanyaann1995
Board Regular
- Joined
- Mar 24, 2021
- Messages
- 62
- Office Version
- 2016
- Platform
- Windows
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.
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.
VBA Code:
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
Attachments
Last edited by a moderator: