0

I am running a simple code, however I get a type mismatch error.

The code is as follows

    For i = 2 To rightcomp
        matchC = Application.Match(Worksheets(2).Cells(1, i), Worksheets(3).Rows(1), 0)
        MsgBox matchC
        For jur = 2 To downlastcomp
            matchr = Application.Match(Worksheets(2).Cells(jur, 1), Worksheets(3).Columns(temp), 0)
            Worksheets(2).Cells(jur, i).Value = Worksheets(3).Cells(matchr, matchC).Value
        Next jur
    Next i

Initially I made a mistake in the following line:

matchr = Application.Match(Worksheets(2).Cells(jur, 1), Worksheets(3).Columns(temp), 0)

Intstead of 

.Cells(jur, 1)

I had 

.Cells(i, 1)

The code worked, however the values that were filled were incorrect. When I corrected the code, thus changed i to jur, the code fills in the first column correctly, however I get an error for the second column. Any ideas why?

Addition:

I have two sheets from different sources. I add a new sheet and reconcile the columns and rows. If the columns and rows are in both sheets I print them in the third sheet.

I then use the code above to fill in the rest of the data in the third sheet. Therefore it cannot be the case that the data is not found.

Also, with the below, the MsgBox is called correctly all the time, yet if I change the below to jur, the MsgBox is called only once and for the firs line.

.Cells(i, 1)

The values in matchC are strings and the values in matchR are numbers and strings.

Also, if comment out the below, the code does not induce Type Mismatch error.

Worksheets(2).Cells(jur, i).Value = Worksheets(3).Cells(matchR, matchC).Value
tadalendas
  • 1,461
  • 3
  • 16
  • 37
  • 1
    You really need to : 1- provide more code, a more complete one, 2- specify what kind of value you are reading – Xarylem Feb 26 '15 at 10:21
  • 1
    While Option Explicit is always good idea it can certainly help under such circumstances to dim all variables. Also, you might want to probe for errors. Note that Application.Match gives you front-end errors while Application.WorksheetFunction.Match gives you VBA runtime errors. – Ralph Feb 26 '15 at 10:30
  • This: http://stackoverflow.com/questions/15526784/why-am-i-getting-error-2042-in-vba-match might 'match' your problem :) – Xarylem Feb 26 '15 at 10:35
  • 1
    You'll get a Type Mismatch if `matchC` is not declared as `Variant` and the lookup value is not found. – Rory Feb 26 '15 at 10:49

0 Answers0