Help with extracting data from range in VBA

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Attachments

  • Screenshot 2021-01-04 171312.png
    Screenshot 2021-01-04 171312.png
    16.6 KB · Views: 3

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
Assuming you mean that Sheets("All").F22 contains the value to look for, then try
VBA Code:
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
 
Solution

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Assuming you mean that Sheets("All").F22 contains the value to look for, then try
VBA Code:
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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:
VBA Code:
Sheets("OutputSheet").Cells(15 + a, "C").Value = .Cells(I, "I").Value

Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,768
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top