Plesae refer to Mod. 2 in the macros, run the mod only to be shown a error message "Type mismatch". Thank you.


Sub MonthlyDispatchedUnits()

Dim Col As Long
Dim LINES As Range, Ln As Range, LnFND As Range
Dim wsMH As Worksheet

Set wsMH = Sheets("Manhood")

With Sheets("Invoice")
Set LINES = .Range("K17").SpecialCells(xlConstants)
Col = wsMH.Cells.Find(Month(.[G1]), LookIn:=xlValues, LookAt:=xlWhole).Column

For Each Ln In LINES
Set LnFND = wsMH.Cells.Find(Ln, LookIn:=xlValues, LookAt:=xlWhole)
Set LnFND = wsMH.Cells.Find("Dispatched", LnFND, LookIn:=xlValues, LookAt:=xlWhole)
If LnFND Is Nothing Then GoTo ErrorExit
wsMH.Cells(LnFND.Row, Col).Value = Ln.Offset(0, 1).Value
Set LnFND = Nothing
Next Ln
End With

Exit Sub

ErrorExit:
MsgBox "Could not find '" & Ln & "' on sheet HO Plan. Update sheet so there is a match."
End Sub

Attached sample.

A few issues here.

1) The sample sheet is devoid of useful data... or any context/explanation to what is going on here.

2) this doesn't make sense to me:

Set LINES = .Range("K17").SpecialCells(xlConstants)

Why use SpecialCells on a single cell? Should there be a much larger range than just K17 for this evaluation? Especially since you are setting up a LOOP later to cycle through all the cells in the range LINES. This needs correction.

3) this doesn't make sense to me:

Col = wsMH.Cells.Find(Month(.[G1]), LookIn:=xlValues, LookAt:=xlWhole).Column
.G1 is empty, part of a merged cell. If you meant that to be on the invoice sheet, remove that leading period, which currently attaches it to the With Sheets("Invoice").