I'm trying to take a cell value from one workbook, and find if it occurs in a column on another workbook, returning a "yes" or "no" answer. I thought the best way to do this would be the Intersect command. I'm getting the "method 'range' of object 'global' failed" error.
Any advice would be welcome.
' Next part puts a "Yes" or "No" in PHO column
' based on one-at-a-time comparison of new keys in column Y
' against Column C of today's PHO report
Dim RowCount As Long
Dim CurrentPHO As Workbook
Dim TodaysDate As String
Dim PHOCompare As Range
Dim CurrentRow As String
Dim CellCounter As Range
TodaysDate = Format(Date, "m-d-yyyy")
Set CurrentPHO = Workbooks.Open("\\netapp02\ProcurementDocs$\PHO\PHO " & TodaysDate & ".xlsx") 'This Opens and defines today's PHO report
Set PHOCompare = CurrentPHO.Worksheets("Detail").Range("C:C")
For RowCount = 2 To LastRow
Set CellCounter = Workbooks("IBUT 11-4-14.xlsx").Sheets("Sheet1").Range("Y" & RowCount)
If Not Intersect(CellCounter, Range(PHOCompare)) Is Nothing Then
Range("K" & RowCount).Value = "No"
Else: Range("K" & RowCount).Value = "Yes"
End If
Next RowCount
Range(PHOCompare)
hardly makes sense ifPHOCompare
is already aRange
. – GSerg