Error trapping for dynamic array

[Dr. Demento]

I'm extraordinarily dense today because I can't figure out how to include error handling when creating a dynamic array that may be empty. The primary project is separating data onto individual sheets; I'm using hiker95's solution found here - awesome work, btw. However, since my arrays can be empty, I need error handling.

There's a multitude of error handling solutions found here, but I have two problems:

1. I don't know how to implement the handling into hiker95's code
2. I don't know which SO solution would be the most universal (GSerg's seems to have limits with string arrays, others only capture Variant arrays, etc).

Any assistance from the Collective would be most welcome.

 

[hiker95]

Dr. Demento,

Please test my latest macro.

And, if you decide that you really need to use arrays, then I can re-write the macro.

 

[Dr. Demento]

I will definitely give it a test run on Monday. Thanks so much!!

Interestingly, that looks like the macro I adapted from Michael M (here) -- that's too awesome!

You're right - I didn't need to use arrays, but I wanted to because even though the current dataset is small, I wanted to make it adaptable to larger datasets and since almost everyone I read talks about the efficiencies/speed of arrays, I thought this was a good project to adapt and learn from. I'm also trying to wrap my head around creating multiple loops to eliminate the ElseIf or Case statements, but that's fodder for another post.

Your efforts and thoughts are much appreciated. I was just trying to learn how to adapt error handling (since I seem to create so many of them, I thought this was a good time to learn about them )

Have a great weekend.

 

[hiker95]

Dr. Demento,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

You're right - I didn't need to use arrays, but I wanted to because even though the current dataset is small, I wanted to make it adaptable to larger datasets and since almost everyone I read talks about the efficiencies/speed of arrays, I thought this was a good project to adapt and learn from.

Do you want me to re-write the macro using arrays, based your latest workbook?

 

[hiker95]

Dr. Demento,

Here is another macro solution for you to consider that uses 5 arrays in memory, and, is based on the current structure of your raw data workbook/worksheets.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Distribute_SRC_Weekly_V3()
' hiker95, 08/14/2015, ME874631
Dim wsrc As Worksheet, wsa As Worksheet, wsaf As Worksheet
Dim wsmc As Worksheet, wsn As Worksheet
Dim sa As Variant, nsa As Long
Dim saf As Variant, nsaf As Long
Dim smc As Variant, nsmc As Long
Dim sn As Variant, nsn As Long
Dim src As Variant, nsrc As Long
Dim i As Long, n As Long, lr As Long, c As Long
Application.ScreenUpdating = False
Set wsrc = Sheets("SRC_Weekly")
Set wsa = Sheets("SA_Weekly")
Set wsaf = Sheets("SAF_Weekly")
Set wsmc = Sheets("SMC_Weekly")
Set wsn = Sheets("SN_Weekly")
lr = wsa.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then wsa.Range("A3:H" & lr).Clear
lr = wsaf.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then wsaf.Range("A3:H" & lr).Clear
lr = wsmc.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then wsmc.Range("A3:H" & lr).Clear
lr = wsn.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then wsn.Range("A3:H" & lr).Clear
With wsrc
  .Activate
  .UsedRange.Cells.WrapText = False
  If .FilterMode = True Then .ShowAllData
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr = 2 Then
    Application.ScreenUpdating = True
    MsgBox ("Sheet SRC_Weekly does not contain any raw data - macro terminated!")
    Exit Sub
  End If
  src = .Range("A3:H" & lr)
  n = Application.CountIf(.Range("D3:D" & lr), "SA")
  If n > 0 Then ReDim sa(1 To n, 1 To 8)
  n = Application.CountIf(.Range("D3:D" & lr), "SAF")
  If n > 0 Then ReDim saf(1 To n, 1 To 8)
  n = Application.CountIf(.Range("D3:D" & lr), "SMC")
  If n > 0 Then ReDim smc(1 To n, 1 To 8)
  n = Application.CountIf(.Range("D3:D" & lr), "SN")
  If n > 0 Then ReDim sn(1 To n, 1 To 8)
End With
For i = LBound(src, 1) To UBound(src, 1)
  If src(i, 4) = "SA" Then
    nsa = nsa + 1
    For c = 1 To 8
      sa(nsa, c) = src(i, c)
    Next c
  ElseIf src(i, 4) = "SAF" Then
    nsaf = nsaf + 1
    For c = 1 To 8
      saf(nsaf, c) = src(i, c)
    Next c
  ElseIf src(i, 4) = "SMC" Then
    nsmc = nsmc + 1
    For c = 1 To 8
      smc(nsmc, c) = src(i, c)
    Next c
  ElseIf src(i, 4) = "SN" Then
    nsn = nsn + 1
    For c = 1 To 8
      sn(nsn, c) = src(i, c)
    Next c
  End If
Next i
If nsa > 0 Then
  wsa.Range("A3").Resize(UBound(sa, 1)).NumberFormat = "@"
  wsa.Range("A3").Resize(UBound(sa, 1), UBound(sa, 2)) = sa
  wsa.Range("F3").Resize(UBound(sa, 1)).NumberFormat = "m/d/yyyy"
  wsa.Range("G3").Resize(UBound(sa, 1)).HorizontalAlignment = xlCenter
  wsa.Columns("A:H").AutoFit
End If
If nsaf > 0 Then
  wsaf.Range("A3").Resize(UBound(saf, 1)).NumberFormat = "@"
  wsaf.Range("A3").Resize(UBound(saf, 1), UBound(saf, 2)) = sa
  wsaf.Range("F3").Resize(UBound(saf, 1)).NumberFormat = "m/d/yyyy"
  wsaf.Range("G3").Resize(UBound(saf, 1)).HorizontalAlignment = xlCenter
  wsaf.Columns("A:H").AutoFit
End If
If nsmc > 0 Then
  wsmc.Range("A3").Resize(UBound(smc, 1)).NumberFormat = "@"
  wsmc.Range("A3").Resize(UBound(smc, 1), UBound(smc, 2)) = sa
  wsmc.Range("F3").Resize(UBound(smc, 1)).NumberFormat = "m/d/yyyy"
  wsmc.Range("G3").Resize(UBound(smc, 1)).HorizontalAlignment = xlCenter
  wsmc.Columns("A:H").AutoFit
End If
If nsn > 0 Then
  wsn.Range("A3").Resize(UBound(sn, 1)).NumberFormat = "@"
  wsn.Range("A3").Resize(UBound(sn, 1), UBound(sn, 2)) = sn
  wsn.Range("F3").Resize(UBound(sn, 1)).NumberFormat = "m/d/yyyy"
  wsn.Range("G3").Resize(UBound(sn, 1)).HorizontalAlignment = xlCenter
  wsn.Columns("A:H").AutoFit
End If
wsrc.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Distribute_SRC_Weekly_V3 macro.

 

[Dr. Demento]

Hiker95,

Both subs work perfectly!! Thank you so much. A simple If Then would never have occurred to me as an error trap; nice!

I did have one question about this line

wsa.Range("A3").Resize(UBound(sa, 1), UBound(sa, 2)) = sa

- what does this do? I'm especially confused because the resize is defined by two upper bound terms; obviously I don't understand the term/use very well.

Thanks again.

 

[hiker95]

Both subs work perfectly!! Thank you so much. A simple If Then would never have occurred to me as an error trap; nice!

Dr. Demento,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

I did have one question about this line

wsa.Range("A3").Resize(UBound(sa, 1), UBound(sa, 2)) = sa

- what does this do? I'm especially confused because the resize is defined by two upper bound terms; obviously I don't understand the term/use very well.

The sa array is written to worksheet wsa, beginning in cell A3.

The sa array has rows, and, columns:

UBound(sa ,1) = the number of rows in the sa array

UBound(sa, 2) = the number of columns in the sa array

I hope that helps you to understand that line of code.

 

[Dr. Demento]

That does; I didn't realize that the format of UBound(array, #) referred to row (1) and column (2). Something new every day.

Thanks again.

 

[hiker95]

Dr. Demento,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.