Macro Generating Run-Time Error 13 Type Mismatch

[Chris Waller]

Hi, Can anyone help me with a macro which was previously working, but when I have move to another PC the macro appears to be generating a Run-Time Error '13' Type Mismatch. I have highlighted in red the text which is where the macro is throwing this error. I just don't know what the problem is. TIA

Sub Search()
Call FindGrade("A3-1G", 10)
Call FindGrade("A4-1G", 41)
Call FindGrade("A6-16G", 99)
End Sub

Sub FindGrade(grade As String, amount)
Dim found As Range

Set found = Sheet1.Range("A1").EntireColumn.Find( _
What:=grade, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If Not found Is Nothing Then
found.Offset(0, 5).Range("A1").FormulaR1C1 = "=ROUND(SUMIF(C[-1],""" & grade & """,C[-2])/7.5,2)"
With found.Offset(0, 1).Range("A1")
.FormulaR1C1 = "=RC[-1]*" & amount
.NumberFormat = "$#,##0.00"
End With
End If
End Sub

 

[wideboydixon]

I think your code needs the active cell to be in column A. I tried it with the active cell in column A and it didn't throw an error. When I selected B2 and then ran the code I also got Type Mismatch. You could add this line before the erroring line:

Cells(ActiveCell.Row, 1).Activate

WBD

 

[Chris Waller]

FAO WBD, Thanks for that. I gave it a whirl and it appears to have resolved the problem. One day I will get the hang of VBA.

 

[RoryA]

FWIW, if you aren't worried about where the searching starts, you can simply omit the After parameter altogether.

 

[Chris Waller]

RoryA,

As there are numerous occurrences of the Grade in Column E, I require the Macro to only check the occurrences that appear in Column A only.

 

[Chris Waller]

FAO WBD, Thanks for your earlier reply. I don't appear to be experiencing the difficulties I had previously, however, the code does not appear to be working as it is not inserting the calculations in the appropriate columns. The actual Sheet name is "Output", which is Sheet8, however the code appears to relate to Sheet1. I have tried changing the code to refer to Sheet8, but, it doesn't appear to like that.

 

[RoryA]

I require the Macro to only check the occurrences that appear in Column A only.

It would still do that. The After parameter only determines which cell in the search range is checked first. So if your search value appears in A1, A5 and A10 and you specify A1 as the after argument, it will find A5 first (assuming a default search direction). It will do exactly the same if you skip the After parameter as it defaults to the first cell in the search range.

 

[wideboydixon]

Perhaps this?

Public Sub Search()

Call FindGrade("A3-1G", 10)
Call FindGrade("A4-1G", 41)
Call FindGrade("A6-16G", 99)

End Sub
Private Sub FindGrade(grade As String, amount)

Dim found As Range

Set found = Worksheets("Output").Columns("A").Find( _
    What:=grade, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False)

If Not found Is Nothing Then
    found.Offset(0, 5).Range("A1").FormulaR1C1 = "=ROUND(SUMIF(C[-1],""" & grade & """,C[-2])/7.5,2)"
    With found.Offset(0, 1).Range("A1")
        .FormulaR1C1 = "=RC[-1]*" & amount
        .NumberFormat = "$#,##0.00"
    End With
End If

End Sub

Note: Untested

WBD