CODEWITHSUNDEEP
vbaexcel
Premal
Premal

Reputation: 531

Type Mismatch Run Time error 13 for excel VBA

I have requirement to write some function which will accept Range as input and I need to return value of first non empty cell. I have tried in one excel sheet and finding non empty cell was working fine. When I try with my project excel file it was not working. Basically for Find method of Range I am getting runtime error 13. Check below code and let me know what is the issue. I have noticed even in when I put Range.Row property it make "Row" as row in code ( in below code see Target.row).

 Sub Btn_GenerateChartClicked()
    If Range("E9") = "Scatter" Then
        MsgBox "Scatter is selected"
        Dim str As String
        Dim rng As Range
        Set rng = Range("B12:I12")
        str = FindNonEmptyCellFromRange(rng)
       ' MsgBox str

    Else
        MsgBox "Bar is selected"
    End If
End Sub


Function FindNonEmptyCellFromRange(Target As Range) As String
Dim ws As Worksheet
Set ws = Sheets("Benchmarking_Project")
Dim foundRange As Range

Set foundRange = Target.Find("*", Cells(Target.row, 1), xlFormulas, , xlByColumns, xlPrevious)
'Dim cellValue As String
'cellValue = foundRange.Value
FindNonEmptyCellFromRange = "Test"
'cellValue


End Function

Upvotes: 0

Views: 1275

Answers (3)

Premal
Premal

Reputation: 531

Ian your suggestion about not to use Cells(Target.Row,1) in Find method is right. I got my mistake. In that I have put column index as 1 but it should be 2 because my selected range is from Column B which means column index 2. So I got actually error because there is no column index 1 in that range. So if I put 2 instead of 1 in above mentioned call then it is working fine. Yes your right that I was not returning actually value of last non empty cell as that was my R&D code I kept changing it. So while posting it I forgot to change it. Thank you all for your reply

Upvotes: 0

Kannan Suresh
Kannan Suresh

Reputation: 4580

Your question does not provide enough details and the function call does not return the non empty cell. Whatever happens your function will return only Test.

Anyway when going through the code, your range has a single row in it.

Issue seems to be with the following code

Set foundRange = Target.Find("*", Cells(Target.row, 1), xlFormulas, , xlByColumns, xlPrevious)

There is no need to specify the After Parameter Cells(Target.row, 1)

After parameters corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

Try to change that code to

Set foundRange = Target.Find("*", , xlFormulas, , xlByColumns, xlPrevious)

The following code may work for you

Sub Btn_GenerateChartClicked()
    If Range("E9") = "Scatter" Then
        MsgBox "Scatter is selected"
        Dim str As String
        Dim rng As Range
        Set rng = Range("B12:I12")
        str = GetFirstNonEmptyCell(rng)
       ' MsgBox str

    Else
        MsgBox "Bar is selected"
    End If
End Sub


Public Function GetFirstNonEmptyCell(Target As Range)
    Dim startCell As Range, firstNonEmptyCell As Range

    For Each c In Target.Cells
        If Trim(c.Value) <> "" Then
            Found_Address = c.Address
            Exit For
        End If
    Next
    GetFirstNonEmptyCell = Found_Address

End Function

Upvotes: 0

LBPLC
LBPLC

Reputation: 1571

You can't find a target.

use Cell.Find and then once you have the cell selected use Target.Address to get the address of the cell

So your CellValue would become:

CellValue = FoundRange.Address

Although, your question is a little vague as your not doing anything practicle with this UDF anyway

Upvotes: 1

Related Questions