Vlookup Error

[kgallego]

Hello all,

Can anyone tell me what my error is? It says "Type Mismatch" and stops on the line "ActCode = Application.VLookup(day, Sheet2.Range("H8:H41"), Sheet2.Range("A8:A41"), True)"

This code is supposed to take numerical value "day", find the closest match (not exact) in Sheet2.Range("H8:H41"), and return the corresponding text (string) from Sheet2.Range("A8:A41") saved as ActCode.

Option Explicit


Sub Filldata()


Dim TDHT As Double
TDHT = Sheet2.Cells(43, 8).Value


Dim i As Integer
For i = 1 To TDHT


Dim ActCode As String
Dim day As Double
day = Sheet3.Cells(10, 4 + i).Value
ActCode = Application.WorksheetFunction.VLookup(day, Sheet2.Range("H8:H41,A8:A41"), Sheet2.Range("A8:A41"), True)
Sheet3.Cells(11, 4 + i).Value = ActCode

Next i


End Sub



Any help would be greatly appreciated.

Thanks,
Kelsey

 

[Norie]

Kelsey

Usually Vlookup takes 4 arguments, the first the value to look for, the second the contiguous range to look in, the third the column within that range to return the value from if there is a match and the fourth to determine which type of search to use.

In your code you have a non-contiguous range for the second argument and a range for the 3 argument.

I think what you probably need is INDEX/MATCH, MATCH to find 'day' in H8:H41 and INDEX to return the corresponding value in A8:A41.

Perhaps something like this.

Sub Filldata()
Dim TDHT As Double
Dim i As Integer
Dim ActCode As String
Dim day As Double
Dim Res As Variant

    For i = 1 To TDHT
    
        TDHT = Sheet2.Cells(43, 8).Value
        day = Sheet3.Cells(10, 4 + i).Value
        Res = Application.Match(day, Sheet2.Range("H8:H41"), -1)
        If Not IsError(Res) Then
            ActCode = Application.Index(Sheet2.Range("A8:A41"), Res)
            Sheet3.Cells(11, 4 + i).Value = ActCode
        End If

    Next i

End Sub

Note, I've taken a guess at -1 for the third argument for MATCH - what to actually use depends on your data, how it's sorted etc.

 

[kgallego]

Kelsey

Usually Vlookup takes 4 arguments, the first the value to look for, the second the contiguous range to look in, the third the column within that range to return the value from if there is a match and the fourth to determine which type of search to use.

In your code you have a non-contiguous range for the second argument and a range for the 3 argument.

I think what you probably need is INDEX/MATCH, MATCH to find 'day' in H8:H41 and INDEX to return the corresponding value in A8:A41.

Perhaps something like this.

Sub Filldata()
Dim TDHT As Double
Dim i As Integer
Dim ActCode As String
Dim day As Double
Dim Res As Variant

    For i = 1 To TDHT
    
        TDHT = Sheet2.Cells(43, 8).Value
        day = Sheet3.Cells(10, 4 + i).Value
        Res = Application.Match(day, Sheet2.Range("H8:H41"), -1)
        If Not IsError(Res) Then
            ActCode = Application.Index(Sheet2.Range("A8:A41"), Res)
            Sheet3.Cells(11, 4 + i).Value = ActCode
        End If

    Next i

End Sub

Note, I've taken a guess at -1 for the third argument for MATCH - what to actually use depends on your data, how it's sorted etc.

Norie, thanks for your response, unfortunately it skipped to "Next i" every time. Maybe it would be more helpful if you saw the table I was pulling data from:

 

[kgallego]

Norie,

When I run the code that you gave me, the "Res" value is assigned Value "Error 2042". Does this make any sense?

Thanks,
Kelsey

 

[kgallego]

Figured it out! In cell H8 there's a function of =G8/24, which is zero. For some reason the code doesn't like it. When I shifted it to start on row 9 everything worked.

Thanks for the help!