Help with extracting data from range in VBA

[JARichard74]

I am trying to find the item in Range P2:P8 that is equal to "Price Criteria" or cell F22, copy the corresponding item in column I and paste it somewhere else lets say C15, C16 for now. This is the code I tried but I get method of object _global failed error on this row. Pretty sure the rest of the code is incorrect but I can't get passed the error. Any assistance appreciated.

Sub GetPriceCriteria()
Dim i As Integer, a As Integer, LastRow As Long
Dim cell As Range
Dim Prep As Worksheet, All As Worksheet

With ThisWorkbook.Sheets("All")
For Each cell In Range("P2:P" & LastRow)
    If Range("P" & cell.Row).Value = Range("F22").Value Then
        Cells(i + 10, "F") = Range("I" & cell.Row)
    End If
Next cell
End With
End Sub

 

[Anthony47]

I didn't understand whether what you call "Price criteria" points to a single row or potentially to several rows; in this latter case, all of the matching rows should be copied and where?

Bye

 

[JARichard74]

I didn't understand whether what you call "Price criteria" points to a single row or potentially to several rows; in this latter case, all of the matching rows should be copied and where?

Bye

If you look at the attachment in my original post, Price Criteria appears twice in Column P (Rows 7 & 8) I need to extract the corresponding values in I7 and I8 and copy them to C15 and C16. Thanks

 

[Anthony47]

Assuming you mean that Sheets("All").F22 contains the value to look for, then try

Sub GetPriceCriteria()
Dim I As Long, a As Integer
'Dim Prep As Worksheet, All As Worksheet   '??
'
With ThisWorkbook.Sheets("All")
    For I = 2 To .Cells(Rows.Count, "P").End(xlUp).Row
        If .Cells(I, "P").Value = .Range("F2").Value Then
            .Cells(15 + a, "C").Value = .Cells(I, "P").Value
            a = a + 1
            If a > 1 Then Exit For
        End If
    Next I
End With
End Sub

Bye

 

[JARichard74]

Assuming you mean that Sheets("All").F22 contains the value to look for, then try

Sub GetPriceCriteria()
Dim I As Long, a As Integer
'Dim Prep As Worksheet, All As Worksheet   '??
'
With ThisWorkbook.Sheets("All")
    For I = 2 To .Cells(Rows.Count, "P").End(xlUp).Row
        If .Cells(I, "P").Value = .Range("F2").Value Then
            .Cells(15 + a, "C").Value = .Cells(I, "P").Value
            a = a + 1
            If a > 1 Then Exit For
        End If
    Next I
End With
End Sub

Bye

Thanks Anthony. Just had to make a couple of corrections. Range("F22") instead of "F2" and Cells(I, "I").Value instead of Cells(I, "P").Value. In reality the values need to be copied in a table on another sheet however since I'm a newb at VBA, I'll see if I can figure it out. Thanks again. Marked as solution

 

[Anthony47]

Thanks Anthony. Just had to make a couple of corrections. Range("F22") instead of "F2" and Cells(I, "I").Value instead of Cells(I, "P").Value. In reality the values need to be copied in a table on another sheet

Glad you catched the errors...
If you have to copy to a different sheet then insert the sheet name into the copying instruction:

Sheets("OutputSheet").Cells(15 + a, "C").Value = .Cells(I, "I").Value

Bye