PDA

View Full Version : Macro captures scrap and submits it to another book. Please Help improve.



ghostbroker2
09-28-2017, 06:35 AM
Here's the macro:

Sub macScrap()
Option Explicit

Dim W, wbSource As Workbook
Dim c As Range
Dim lngR As Long, n As Long

Set wbSource = ActiveWorkbook
Set W = Application.Workbooks.Open("\\RWCAD\Dashboards\Extrusion Tracking TEST.xlsx")

For n = 3 To 5
Set c = W.Sheets(1).Range("B4:B18").Find(wbSource.Sheets(1).Range("I" & n).Value)
If Not c Is Nothing Then
lngR = c.Row
With W.Sheets(1).Cells(lngR, "P")
.Value = .Value + _
wbSource.Sheets(1).Range("K" & n).Value
End With
Else
MsgBox wbSource.Sheets(1).Range("I" & n).Value & " Was Not Found!"
End If
Next n
W.Close True

Call macPrint

End Sub


The dilemma here is that not all orders have scrap.
Not all orders have 3 extruders.
If there is no scrap, I don't want it combing these workbooks, as it is replying "Was not found!" three times in these circumstances.
If there is only one extruder, I don't want it looking for the other two, as it is replying "Was not found!" twice in these circumstances.
If there is 2 extruders, I don't want it to look for a third, as it is replying "Was not found!" in these circumstances.

Your help is greatly appreciated.
Thank you.

mdmackillop
09-28-2017, 06:56 AM
Not tested

For Each cel In wbSource.Sheets(1).Range("I3").Resize(3).SpecialCells(2)
Set c = Sheets(2).Range("B4:B18").Find(cel.Value)
If Not c Is Nothing Then
lngR = c.Row
With W.Sheets(1).Cells(lngR, "P")
.Value = .Value + _
wbSource.Sheets(1).Range("K" & cel.Row).Value
End With
Else
MsgBox wbSource.Sheets(1).Range("I" & cel.Row).Value & " Was Not Found!"
End If
Next cel


Suggestions: Avoid merged cells. Use Center Across Selection if required.
Don't post protected sheets requiring passwords.