Runtime error 13 Type mismatch

Hi All,

I was using the below code to extract data and pasting it in another sheet called Result

Now today I added 2 extra rows and I am getting the Type mismatch error at line "Set frng = Sheets("result").Range("A:A").Find(CDate(fdate))"

I have tried amending the i = 1137 because from thats where the 2 rows are being added and hence amended the Irow Step 14
But it still gives the same error

Can anybody please help to troubleshoot

Sub TrfrData()
Dim cell As Range, frng As Range
Dim i As Integer, lrow As Integer, nrow As Integer, x As Integer
Dim fdate As String
lrow = Sheets("2017 SOH").Range("B" & Sheets("2017 SOH").Rows.Count).End(xlUp).Row
For i = 22 To lrow Step 12
fdate = Format(Sheets("2017 SOH").Range("B" & i).Value, "dd-mm-yy")
Set frng = Sheets("result").Range("A:A").Find(CDate(fdate))
If frng Is Nothing Then
With Sheets("result")
nrow = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & nrow).Value = Sheets("2017 SOH").Range("B" & i).Value
For x = 5 To 13
.Cells(nrow, x - 3).Value = Sheets("2017 SOH").Cells(i - 1, x).Value
Next x
For x = 16 To 18
.Cells(nrow, x - 5).Value = Sheets("2017 SOH").Cells(i - 1, x).Value
Next x
End With
End If
Next i
End Sub

Hi logistixexcel

Your steps are 12, 14, 13
Is this the norm and are they repeated....

The problem is that fdate is an empty string because B22 is empty.

Hi- Not sure what the FORMAT >> CDATE thing was about. I took it out and the type mismatch disappeared.

For i = 22 To lrow Step 12

fdate = Sheets("2017 SOH").Range("B" & i).Value
Set frng = Sheets("result").Range("A:A").Find(fdate)

If frng Is Nothing Then

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Sort out your 14 step and then this code has been tested and works:
So date in row 2 then 16 then 30

Option Explicit
Sub TrfrData()
Dim cell As Range, frng As Range
Dim i As Long, lrow As Long, nrow As Long
Dim fdate As String, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("2017 SOH")
lrow = ws.Cells(Rows.Count, "B").End(xlUp).Row
With ws
For i = 2 To lrow Step 14
    fdate = Format(ws.Range("B" & i).Value, "dd-mmm-yy")
    With .Range("B:B")
        Set frng = .Find(What:=fdate, _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
        If Not frng Is Nothing Then
            nrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheet2.Cells(nrow, 1) = fdate
            .Range(.Cells(i + 10, 3), .Cells(i + 10, 11)).Copy Sheet2.Cells(nrow, 2)
            .Range(.Cells(i + 10, 14), .Cells(i + 10, 16)).Copy Sheet2.Cells(nrow, 11)
        End If
    End With
Next i
End With
Application.ScreenUpdating = True
End Sub

Glad we could help. Thanks for the rep point.

mdleclerc

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

PS It's unlikely the errors you are receiving are a version issue.