CODEWITHSUNDEEP
vbatypesrangemismatch
Eva
Eva

Reputation: 917

VBA Type mismatch, Run-time error 13

I just wrote a small VBA function that looks as below. As function output, I would like to get the Range

Public Function selectRows(col As String) As Range

Dim begin, fini As Integer
Set TopCell = Cells(1, col)
Set BottomCell = Cells(Rows.Count, col)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
begin = TopCell.Row
fini = BottomCell.Row
Set selectRows = Activesheet.Range(col & begin & ":" & col & fini)
End Function

Then I get a Type mismatch error when it tries to set output =Range(...)

Could you please help me to fix this issue, thx a lot in advance

Upvotes: 0

Views: 1851

Answers (2)

Jon49
Jon49

Reputation: 4606

You're putting in an input that is greater than the number of columns that you have. For example, my Excel 2003 has IV columns, when I put in IW it throws the Error 13. I'll leave my code below if you want to clean your code up. Some error handling would help so this wouldn't happen again.

Public Function selectRows(ByVal sColumn As String) As Range

    Dim lColumn As Long
    Dim TopCell As Range, BottomCell As Range
    Dim wks As Worksheet

    Set wks = ActiveSheet

    lColumn = wks.Range(sColumn & "1").Column

    Set TopCell = wks.Cells(1, lColumn)
    Set BottomCell = wks.Cells(Rows.Count, lColumn)
    If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
    If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
    Set selectRows = wks.Range(TopCell, BottomCell)

End Function

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91366

It is a very bad idea to use a reserved word such as column as the name of your variable. If you want the function to return a range, you want:

Set selectRows = Range(scol & TopCell.Row & ":" & scol & BottomCell.Row)

Rather than:

Set output = Range(column & TopCell.Row & ":" & column & BottomCell.Row)

There are other problems, such as if the column is empty.

Upvotes: 2

Related Questions