CODEWITHSUNDEEP
vbaexcel
user8514104
user8514104

Reputation:

VBA VLookup cells on different worksheets

I am trying to write a code that checks if two cells on two different worksheets are the same. And, if they are the same, uses VLOOKUP to find the difference between assigned values for those cells (which are in a different column)

This is what I have written so far, and I keep getting the error message

Object doesn't support this property or method

and can not figure out why. Can anyone give input? Thanks! 

Sub testing()

Product = Range("A2:A506")
Set myrange = Worksheets("open_prices").Range("A2:D506")
Set myrange2 = Worksheets("close_prices").Range("A2:B506")

Dim popen As Integer
Dim pclose As Integer

Dim ws As Worksheet

For Each Cell In Workbooks("TDO VBA Test.xlsx")
    If Worksheets("open_prices").Range("A2:A506") = Worksheets("close_prices").Range("A2:A506") Then
            popen = WorksheetFunction.VLookup(Product, myrange, 4, False)
            pclose = WorksheetFunction.VLookup(Product, myrange2, 2, False)
            result = popen - pclose
    End If
Next Cell

End Sub

Upvotes: 1

Views: 106

Answers (1)

YowE3K
YowE3K

Reputation: 23994

I am guessing that this is what you are trying to do, i.e. iterate over each cell in one sheet's A2:A506 and see if the value is in the other sheet and, if it is, do the calculation.

Sub testing()

    Dim popen As Integer
    Dim pclose As Integer
    Dim result As Integer ' Are you sure these are integers?  Usually prices will be Double

    Dim VLookupResult As Variant
    Dim cel As Range

    For Each cel In Worksheets("open_prices").Range("A2:A506").Cells
        popen = cel.Offset(0, 3).Value
        VLookupResult = Application.VLookup(cel.Value, Worksheets("close_prices").Range("A2:B506"), 2, False)
        If Not IsError(VLookupResult) Then
            pclose = VLookupResult
            result = popen - pclose
            ' You will need to decide what to do with this "result" now
            ' that it has been calculated
        End If
    Next cel

End Sub

Upvotes: 2

Related Questions