Error handling for cells.find

Revised and truncated version of my last question. I updated how I handle the error if a cost center is not found. I originally asked expecting improvements on the matching system so thoughts on that will also be appreciated. Finally, interested in improvements to the looping part to determine first blank cell to drop data (the line starting with For y = 0 To 99).

Of course, surprise me with things I don't even think about ^_^;

```
Sub transfer()

Dim actualsWS As Worksheet
Dim fromWS As Worksheet
Dim inputMonth As Integer
Dim month As String
Dim loc As Range
Dim start As Integer
Dim rowCol As Variant
Dim dropRow As Integer
Dim dropCol As Integer
Dim locActual As Range

'store worksheets into variables
Set actualsWS = ThisWorkbook.Worksheets("Actuals by Month")
Set fromWS = ThisWorkbook.Worksheets("FAS Data Entry")

MsgBox ("If macro dies, it's likely a missing cost center.") 'warning for user

inputMonth = InputBox(prompt:="What month?") 'prompt user for current month
month = ToMonthName(inputMonth) 'call function to turn number into string
If month = vbNullString Then 'if bad month, quit sub
MsgBox ("bad month")
Exit Sub
End If

With fromWS 'data entry tab

Set loc = .Cells.Find(month) 'locate starting range for current month

'check previous row for carried over month (revising asset) Will break if 2 or more
If loc.Offset(-1, 0) = "" Then 'if previous row is blank
start = loc.row 'starting row is same row as found location above
Else
start = loc.row - 1 'else it's 1 row above
End If

i = 0
Dim data As Collection
Set data = New Collection

Do While .Cells(start + i, 1) <> vbNullString 'loop through column A while there is a month present (not blank)

'passes entire row to function then add it to collection
data.Add ReadModel(.Range("A" & start + i).EntireRow)
i = i + 1

Loop

End With

With actualsWS 'actuals tab

.Columns("V:W").ClearContents 'clear previous data

This function will either return "" or the name of month.

Function getMonth() As String
    Dim msg As String
    Dim m As String
    Dim i As Integer
    msg = "What Month?"
    For i = 1 To 12
        msg = msg & vbCrLf & i & " - " & MonthName(i)
    Next

    i = Application.InputBox(msg, "Choose a Month", month(Date), , , , , 1)

    If i > 0 Then getMonth = MonthName(i)
End Function

ReadModel(.Range("A" & start + i).EntireRow)

ReadModel is miss leading.

• getModel()

• getNewModel()

• createModel()

• getModelFromRow()

Replace .Range("A" & start + i).EntireRow with .Rows(i)

The failed collection is not necessary.


Dim failed As Collection

If you are planning on using the data collection elsewhere, add a Found property to your DataModel.

Private Type TModel
    CostCenter As String
    Description As String
    Amount As Single
    Found as Boolean
End Type

If you are not going to reuse the data collection, just remove the items form data as you find them.


item.Remove data
Is this your Output:

Actuals by Month


Cost Center | Item Amount | Item Description
Janitorial

.....Possibly 99 blank rows later
Front Office

$45.00 12 Reams Standard Letter Size
$10.34 24 Red Pens
$23.56 1 Case Sticky Pads
.....Possibly 96 blank rows later
Sales Dept
$45.00 12 Reams Standard Letter Size
$123.99 10 Cases Type 2 Invoices
.....Possibly 97 blank rows later
Facilities

.....Possibly 99 blank rows later


Couldn't this be achieved by filtering your FAS Data Entry by month and applying standard Excel grouping and subtotaling techniques?

I would recommend clearing the Cost Centers off of the [Actuals by Month] and writing them in from row 1 as you iterate through the data collection. If you need a complete list of Cost Centers, simply add them to the data collection in your initial loop.

StackExchange Code Review Q#132665, answer score: 3