VBA VLookup Function with Loop....Runtime Error - Type Mismatch

[sarah_g]

I am attempting to do the following:

1. Look at the first row's column B (Event) from entiresalespipeline
2. Match Column B to a table (Events_and_Activities) that links a date to each event
3. If that date is in the future, copy the entire row into the next empty row in a third worksheet (CurrentSalesPipeline)
4. Repeat this process until there are no more filled rows in the first spreadsheet.

I have created the following code, which when I mouse-over the text appears to give correct data for the first row of the original spreadsheet, but which gives me the following error: "Run-time error '13': Type mismatch".

I am also not sure if the large block at the end will work properly and iterate to input the correct row data (rather than just repeating the first line each time), but I can't get to that bit until the runtime error is solved.

Any help for these issues would be greatly appreciated.

Thanks, Sarah

<code>Sub ShowUpcoming_Click()

Dim rCell As Range

Dim ws As Worksheet

Dim DateConf As Long

For Each rCell In Sheet1.Range("B3:B5000")

Set ws = Worksheets("CurrentSalesPipeline")

DateConf = Application.VLookup(rCell, Worksheets("Events_and_Activities").Range("A2:B12"), 2, False)
Range("A1").Value = DateConf

If CDate(DateConf) >= CDate((Date)) Then
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 1).Value = Worksheets("entiresalespipeline").Range("A3:A3").Value
.Cells(iRow, 2).Value = Worksheets("entiresalespipeline").Range("B3:B3").Value
.Cells(iRow, 3).Value = Worksheets("entiresalespipeline").Range("C3:C3").Value
.Cells(iRow, 4).Value = Worksheets("entiresalespipeline").Range("D3:D3").Value
.Cells(iRow, 5).Value = Worksheets("entiresalespipeline").Range("E3:E3").Value
.Cells(iRow, 6).Value = Worksheets("entiresalespipeline").Range("F3:F3").Value
.Cells(iRow, 7).Value = Worksheets("entiresalespipeline").Range("G3:G3").Value
.Cells(iRow, 8).Value = Worksheets("entiresalespipeline").Range("H3:H3").Value
.Cells(iRow, 9).Value = Worksheets("entiresalespipeline").Range("I3:I3").Value
.Cells(iRow, 10).Value = Worksheets("entiresalespipeline").Range("J3:J3").Value
.Cells(iRow, 11).Value = Worksheets("entiresalespipeline").Range("K3:K3").Value
.Cells(iRow, 12).Value = Worksheets("entiresalespipeline").Range("L3:L3").Value
.Cells(iRow, 13).Value = Worksheets("entiresalespipeline").Range("M3:M3").Value
.Cells(iRow, 14).Value = Worksheets("entiresalespipeline").Range("N3:N3").Value
.Cells(iRow, 15).Value = Worksheets("entiresalespipeline").Range("O3:O3").Value
.Cells(iRow, 16).Value = Worksheets("entiresalespipeline").Range("P3:P3").Value
.Cells(iRow, 17).Value = Worksheets("entiresalespipeline").Range("Q3:Q3").Value
.Cells(iRow, 18).Value = Worksheets("entiresalespipeline").Range("R3:R3").Value
.Cells(iRow, 19).Value = Worksheets("entiresalespipeline").Range("S3:S3").Value
.Cells(iRow, 20).Value = Worksheets("entiresalespipeline").Range("T3:T3").Value
End With
End If
Next rCell
End Sub </code>

 

[mole999]

if you change <code>Dim DateConf As Long to <code>Dim DateConf does it work, just wondering how the dates are working</code></code>

 

[sarah_g]

Then the error moves down to the "If CDate(DateConf) >=...." line instead as a type mismatch.

Do I need to create different variables for the lookup function and the CDate functions?

Thanks!

if you change <code>Dim DateConf As Long to <code>Dim DateConf does it work, just wondering how the dates are working</code></code>