Type Mismatch in Lookup Statement 1

[krispi]

Hi folks

I have written a piece of code to populate cells from a source data file. There will be no more than 12 rows of data in the source file, one row per month. However, sometimes one or more months will not produce any data and the number of rows of data will be fewer. My code works fine when the source data contains 12 rows, but when it doesn't, I get a type mismatch at the point where the lookup value is not found in the source file. Any ideas why this is happening? Here's the code...

    Dim intLookUpValue As Integer
    Dim targetRow As Integer
    Dim sourceRow As Integer
    Dim rngLookUpRange As Range
    Dim myVar As Variant
    Dim wbMprCases As Workbook
    Dim wsSource As String
        
        targetRow = 7
        sourceRow = 2
        intLookUpValue = 0
        
        Set wbMprCases = ThisWorkbook
        Set wb1 = Workbooks.Open(wbMprCases.Worksheets("Sheet1").Range("AB16").Text)
        wsSource = wbMprCases.Worksheets("Sheet1").Range("AB17")
        Set rngLookUpRange = wb1.Worksheets(wsSource).Range("A2:d14")
            
            Do While intLookUpValue < 13
            
            If IsError(myVar = Application.VLookup(intLookUpValue, rngLookUpRange, 3, False)) Then
            myVar = 0
            Else: myVar = Application.VLookup(intLookUpValue, rngLookUpRange, 3, False)
            End If
            If myVar = 0 Then
            wbMprCases.Worksheets("Sheet1").Cells(targetRow, 22).Value = 0
            Else: wbMprCases.Worksheets("Sheet1").Cells(targetRow, 22).Value = myVar
            End If
            
            targetRow = targetRow + 2
            sourceRow = sourceRow + 1
            intLookUpValue = intLookUpValue + 1
            
            Loop
        wb1.Close

 

[SkipVought]

Typically, if the lookup value does not exist in the lookup table, the lookup result is an ERROR value.

1. Make the variable that you assign the lookup result a Variant.

2. Check the return value for an error. If an error then just skip to the next lookup value.

3. This is really a spreadsheet issue, and not a VBA issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krispi]

Hi Skip

thanks for the reply. However, I thought my code was already doing numbers 1 & 2 in your reply...

Thanks again

Chris

 

[SkipVought]

Please point out the statement that is the problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krispi]

OK. The source dataset looks like this
1 10 50
2 10 10
3 20 60
4 10 10
5 20 10
6 10 70
7 10 40
8 30 30
9 20 10
12 10 10

It loops through fine until the value of IntLookUpValue hits 10, then the error is triggered at the statement

If IsError(myVar = Application.VLookup(intLookUpValue, rngLookUpRange, 3, False)) Then MyVar=0

Appreciate your help.
Chris

 

[SkipVought]

    Dim intLookUpValue as integer, MyVar
    
    intLookUpValue = 10
    
    MyVar = Application.VLookup(intLookUpValue, [rngLookUpRange], 3, False)
    
    If IsError(MyVar) Then MyVar = 0

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krispi]

Thanks Skip.

 

[krispi]

Doh! That'll be me trying to overcomplicate things (not for the first time...)

Thanks Skip