Type mismatch:Runtime error 13

[shalskedar]

For r = 2 To 28
 
 
Do While Len(Range("A" & r).Formula) > 0
rs.AddNew
rs.Fields(0) = Range("A" & r).Value
rs.Fields(1) = Range("C" & r).Value
'Debug.Print r & ":(" & Range("D" & r).Value & ")"
rs.Fields(2) = CLng(Range("D" & r).Value)
rs.Fields(3) = Range("G" & r).Value
rs.Fields(4) = Range("J" & r).Value
 'rs.Fields(5) = CLng(Range("L" & r).Value)
'Debug.Print CLng(Range("L" & r).Value)
rs.Fields(6) = CLng(Range("O" & r).Value)rs.Update
r = r + 1
Loop
Next r

As seen in the attached file the first 2 record are inserted in the table.But Im getting Type mismatch error for the highlighted statements.

Can anyone Plz help me to rectify this..

[NeoPa]

Please ask a clear question without requiring us to go round the houses just to work out what you want.

A good start in an Access / VBA forum is to make it clear you are working in Excel.

[ADezii]

There are more than likely 2 possible explanations:

1. The Values in the specific Column(s) where the Error(s) occur cannot be coerced to a LONG Data Type.
2. The underlying Field in the Table representing the Recordset is not a comparable Data Type to LONG.
3. If neither of these solve the problem, Attach the Spreadsheet if possible, and we'll have a look at it.

[shalskedar]

Can u just go thru the attached file...

In this file the first 2 records are getting inserted but it gives me error for the columns "Gsteel" & last column "Qty"..

The datatype for both this columns is Long..So how do i rectify these code becoz i m getting error for these lines of code.

rs.Fields(5) = CLng(Range("L" & r).Value)
Debug.Print CLng(Range("L" & r).Value)
rs.Fields(6) = CLng(Range("O" & r).Value)

.

[ADezii]

It may be getting hung up because of NULLS in the 2 Fields mentioned, try:

rs.Fields(5) = Nz(Range("L" & r).Value)
rs.Fields(6) = Nz(Range("O" & r).Value)

[shalskedar]

Thanks alot ADezii...but can u Plz tell me how to use Nz function in vba code as Excel does not support Nz fuction..

Can u Plz let me know...

[ADezii]

At this point, I'm simply guessing. How about Uploading the Spreadsheet with the accompanying code, and I'll be glad to look at it.

[NeoPa]

Nz() is an Access function.

If you add the Microsoft Access library reference to your Excel project you will be able to use Nz().

[vanarova]

Add References some is missing..