Run-time errors handling in VBA

[smide]

Hello.

I need some assistance with my VBA code which extract numbers under certain conditions (every 2 minutes) to Sheet4 from data report in row 1 - Sheet5.
I'm not very proficient with excel-vba but I'm open to suggestions and willing to learn to get this to work properly.

Basically, my report sheet (row 1, Sheet5) is updated also periodically (every 1-2 min), but sometimes I'm receiving just a blank (empty) report and in that case when my VBA (extract) code runs pop-up message appears and code stops:

Run-time error '13':
Type mismatch

- logical, because there is no data for processing (Sheet5 is empty)...

I don't know is it possible to but I would like to insert an additional part of code which will wait for (let say) 45 seconds (in the case of Run-time error) and then run the whole code again without pop up error message.

Here is my code I've used so far:

Sub Salary ()
Dim ar As Variant
Dim home As Variant
Dim away As Variant

ar = Application.Transpose(Application.Transpose(Sheets("Sheet6").Range("A1:YQ1")))

shop = Split(Replace(Replace(Join(Filter(ar, "home:"), "~"), "home:", ""), """", ""), "~")
stor = Split(Replace(Replace(Join(Filter(ar, "away:"), "~"), "away:", ""), """", ""), "~")

Sheets("Sheet4").Range("B3").Resize(UBound(shop) + 1) = Application.Transpose(shop)
Sheets("Sheet4").Range("C3").Resize(UBound(stor) + 1) = Application.Transpose(stor)

Dim C As Long, x As Long, Data As Variant, Result As Variant
  Data = Sheets("Sheet6").Range("A1", Sheets("Sheet6").Cells(1, Columns.Count).End(xlToLeft))
  ReDim Result(1 To UBound(Data, 2), 1 To 1)
  For C = 1 To UBound(Data, 2)
    If Data(1, C) Like "*""[Ii][Dd]"":#*" And _
       Left(LCase(Data(1, C)), 14) <> "league:[{""id"":" And _
       Left(LCase(Data(1, C)), 15) <> "leagues:[{""id"":" Then
      x = x + 1
      Result(x, 1) = Mid(Data(1, C), InStrRev(Data(1, C), ":") + 1)
    End If
  Next
  Sheets("Sheet4").Range("A3").Resize(UBound(Result)) = Result

Application.OnTime Now + TimeValue("00:02:00"), "Salary"

End Sub

 

[gallen]

At what line do you get the error? Could you not just check if it is blank, and if it is do nothing?

 

[smide]

At what line do you get the error? Could you not just check if it is blank, and if it is do nothing?

Error (in the case of empty Sheet5) appears at line: Sheets("Sheet4").Range("B3").Resize(UBound(shop) + 1) = Application.Transpose(shop)
Yes, "do nothing" is also possible option, I would like just to avoid pop up error message and keep code run/update every 2 minutes.

 

[gallen]

I dont see 'shop' as a variable declared so I assume it is declared at module level and it is an array

So would you need to check every element of the array to see if it has a value or would you just need to check the first element?

 

[smide]

I dont see 'shop' as a variable declared so I assume it is declared at module level and it is an array

So would you need to check every element of the array to see if it has a value or would you just need to check the first element?

Actually, You gave me an idea how to solve this with a simple: On Error Resume Next , it will do the trick...