Consulting

Results 1 to 2 of 2

Thread: Macro captures scrap and submits it to another book. Please Help improve.

  1. #1

    Macro captures scrap and submits it to another book. Please Help improve.



    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.
    Attached Files Attached Files

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •