CODEWITHSUNDEEP
excelvba
briguy
briguy

Reputation: 65

VBA Excel: Finding a match between two different worksheets

I have written some code that is trying to match an ID ("ISIN") that is present on worksheet "A and confirm if it's worksheet ""B. If there is a match, I would like to take the corresponding inputs/values on worksheet "B" and place it on worksheet "A". As of the moment, my condition is not letting me move forward and the vlookup isnt functioning too.

Any tips?

'For loop to find the corresponding inputs associated with the ISIN
Dim rgRow As Range
For Each rgRow In datarange.Rows
    
    sISIN = rgRow.Cells(1, 2)
    sInstrument = rgRow.Cells(1, 3)
    sType = rgRow.Cells(1, 4)
    sRegion = rgRow.Cells(1, 6)
    sAssetClass = rgRow.Cells(1, 7)
    sRating = rgRow.Cells(1, 8)
    sRatingDynamic = rgRow.Cells(1, 9)
    sProfil = rgRow.Cells(1, 10)

    ISINmatch = Application.Match(sISIN, listrange.Columns(1), 0)
    
    If sType = "Securities" And sISIN <> "" And Not IsError(ISINmatch) Then
        
            sInstrument = Application.WorksheetFunction.VLookup(sISIN, listrange, 2, False)
            sRegion = Application.WorksheetFunction.VLookup(sISIN, listrange, 3, False)
            sAssetClass = Application.WorksheetFunction.VLookup(sISIN, listrange, 4, False)
            sRating = Application.WorksheetFunction.VLookup(sISIN, listrange, 5, False)
            sRatingDynamic = Application.WorksheetFunction.VLookup(sISIN, listrange, 7, False)
            sProfil = Application.WorksheetFunction.VLookup(sISIN, listrange, 6, False)
            

    End If

 Next rgRow

Upvotes: 1

Views: 160

Answers (1)

norie
norie

Reputation: 9867

It's not clear what some of the ranges in your code e.g. DataRange and listrange, refer to but this code should put the relevant values on the worksheet when sISIN is found.

Note, you don't really need the variables,you could assign the results from all the Application.Vlookup calls directly to the cells.

Also, I suggested in a comment not using Application.Vlookup as Application.Match will return the row sISIN and you could use that to get the values you want.

I would still recommend using that but unfortunately can't really post any code to show how it would work because of the aforementioned problems with the ranges used in the code.

Anyway, here's the code.

    'For loop to find the corresponding inputs associated with the ISIN
Dim rgRow As Range

    For Each rgRow In DataRange.Rows
    
        sISIN = rgRow.Cells(1, 2)
        sType = rgRow.Cells(1, 4).Value
        
        If sType = "Securities" And sISIN <> "" Then
        
            ISINmatch = Application.Match(sISIN, listrange.Columns(1), 0)
    
            If Not IsError(ISINmatch) Then
        
                sInstrument = Application.WorksheetFunction.VLookup(sISIN, listrange, 2, False)
                sRegion = Application.WorksheetFunction.VLookup(sISIN, listrange, 3, False)
                sAssetClass = Application.WorksheetFunction.VLookup(sISIN, listrange, 4, False)
                sRating = Application.WorksheetFunction.VLookup(sISIN, listrange, 5, False)
                sRatingDynamic = Application.WorksheetFunction.VLookup(sISIN, listrange, 7, False)
                sProfil = Application.WorksheetFunction.VLookup(sISIN, listrange, 6, False)
            
                rgRow.Cells(1, 3).Value = sInstrument
                rgRow.Cells(1, 4).Value = sType
                rgRow.Cells(1, 6).Value = sRegion
                rgRow.Cells(1, 7).Value = sAssetClass
                rgRow.Cells(1, 8).Value = sRating
                rgRow.Cells(1, 9).Value = sRatingDynamic
                rgRow.Cells(1, 10).Value = sProfil

            End If

        End If

    Next rgRow

Upvotes: 2

Related Questions