This Application.Match is returning a type mismatch error

1
votes

Run-time error '13' (Type mismatch) is returned The code reads:-

TavgRow = Application.Match(ThisStation, ThisSheet.Range("A1:A115083"), 0)

where

Dim TavgRow As Single
Dim ThisStation As String

The range "A1:A115083" is formated as 'Text'.

If I change the code to read:-

TavgRow = Application.Match("ITE00100554", ThisSheet.Range("A1:A115083"), 0)

it returns without error.

I have tried setting the range to 'General' and the variable 'ThisStation' to 'Variant' but the error persists.

Any suggestions would be appreciated.

To give a more full picture of the code:-

Do While Not EOF(1)
        
        Input #1, ThisStation
        Input #1, TheDate
            If (ThisStation <> OldStationID Or TheDate <> TheOldDate) And TAVG <> -9999 Then
                
                'Put the data into the spreadsheet and set bits to Zero
                TavgRow = Application.Match(ThisStation, ThisSheet.Range("A1:A115083"), 0)
                TavgColumn = Application.Match(YearMonth, ThisSheet.Range("A1:NE1"), 0)
                
                If TAVG <> 0 Then TAVG = TAVG / 10
                ThisSheet.Cells(TavgRow, TavgColumn) = TAVG
                
                TMAX = -9999
                TMIN = -9999
                TAVG = -9999
            End If
                
            OldStationID = ThisStation
            TheOldDate = TheDate
            
            TheYear = Left(TheDate, 4)
            TheMonth = Mid(TheDate, 5, 2)
            TheDay = Right(TheDate, 2)
            YearMonth = TheDay & "," & TheMonth

It all works perfectly except for this one line of code!!!

1
excelvba
Where have you defined ThisStation? – JMP
Just to eliminate the obvious..You have assigned a value to ThisStation, right? – Stavros Jon
The first value that it gets assigned is "ITE00100554" which is why I used that as a trial. – AnthonyJ
JMP not quite sure how to answer your question other than as given above Dim ThisStation As String – AnthonyJ
We can't see that part in the code you've posted, that's why I'm asking. Are you tracking down the value of ThisStation, with a debug.print for example, to see what value it has when the code crashes? – Stavros Jon

1 Answers

0
votes

You need to force ThisStation to be read as a string.

Use:

TavgRow = Application.Match((ThisStation), ThisSheet.Range("A1:A115083"), 0)

(with brackets around ThisStation) instead.

Ref: