Converting Index Match to Another Sheet to VBA Code

Question

I am trying to translate an index match formula into VBA code. I would like to do this because the cells that contain the index match formulas will not always have the same row values, so I can't simply place the formulas in specific rows. The formula is currently in column H, so I am trying to have VBA code that will match the values from the other sheet, and populate column H in the correct row based on the index match criteria.

Here is my formula:

=IFERROR(INDEX(Comments!$K$2:$K$76,MATCH(C8,Comments!$B$2:$B$76,0)),"COMMENT REQUIRED")

I initially tried adapting to be a V-Lookup in VBA code as I was told this would be easier, but I have not been able to do this successfully. The VBA code that I tried is below:

        Dim h
        With DestinationSheet.Rows(DestinationRow)
        h = Application.VLookup(.Cells(3).Value, Sheets("Comments").Range("$A$2:$C$100"), 3, False)
        .Cells(8).Value = IIf(IsError(h), "COMMENT REQUIRED", h)
        .Cells(8).Font.Color = IIf(IsError(h), RGB(255, 0, 0), h)
        .Cells(8).Font.Bold = IIf(IsError(h), True, h)
        End With

Scott Craner · Accepted Answer · 2019-02-05 23:19:04Z

Your main issue is these two lines:

    .Cells(8).Font.Color = IIf(IsError(h), RGB(255, 0, 0), h)
    .Cells(8).Font.Bold = IIf(IsError(h), True, h)

h returns an error or a value, but you are trying to use it as a RGB Color and a Boolean. h cannot be all three things at the same time.

Capture the error before and use a standard IF Then

Dim DestinationSheet As Worksheet
Set DestinationSheet = Worksheets("Sheet1") 'change to your sheet

Dim cmmtsSheet As Worksheet
Set cmmtsSheet = Worksheets("Comments")

Dim DestinationRow As Long
DestinationRow = 3

With DestinationSheet

    Dim mtchRow As Long
    On Error Resume Next
        mtchRow = Application.Match(.Cells(DestinationRow, 3), cmmtsSheet.Range("A:A"), 0)
    On Error GoTo 0

    With .Cells(DestinationRow, 8)
        If mtchRow > 0 Then
            .Value = cmmtsSheet.Cells(mtchRow, 3)
            .Font.Color = RGB(255, 255, 255)
            .Font.Bold = False
        Else
            .Value = "Comment Required"
            .Font.Color = RGB(255, 0, 0)
            .Font.Bold = True
        End If
    End With
End With

Collectives™ on Stack Overflow

Converting Index Match to Another Sheet to VBA Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related