Check out some similar questions!

rsdjimbie · Oct 11, 2011, 11:41 PM

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

Code:

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

rsdjimbie · Oct 11, 2011, 11:49 PM

Attached sample.

JBeaucaire · Oct 12, 2011, 01:05 AM

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:

Code:

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:

Code:

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").