Snasui Excel Forum

Code: Select all

 
Dim rBom As Range, rBomAll As Range
For Each rBom In rBomAll
       With Sheets("search")
       Sheets("result").Range("A1").Value = "PARENT"
        Sheets("result").Range("B1").Value = "CHILD"
          If rBom = .Range("A2") And rBom.Offset(0, 6).Value <= .Range("B2").Value And rBom.Offset(0, 7).Value >= .Range("B2").Value Then
             [color=#BF80FF]If rBom.Offset(0, 2).Value <> rBom.Offset(0, 0).Value Then[/color]
             Sheets("result").Range("B" & .Rows.Count).End(xlUp).Offset(1, 0) = rBom.Offset(0, 2)
             End If
             End If
      End With
      Next rBom

Code: Select all

Private Sub CommandButton1_Click()
    Dim rBom As Range, rBomAll As Range, c As Range
    
    Set rBom = ActiveSheet.UsedRange
    If rBom.Count = 1 Then
        rBom = UCase(rBom)
    Else
        For Each c In rBom.SpecialCells(xlCellTypeConstants, xlTextValues)
            c.Value = UCase(c.Value)
        Next
    End If
    
    With Sheets("M_BOM")
        Set rBomAll = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        Sheets("result").Range("A2") = Sheets("search").Range("A2")
        Sheets("result").Range("A1").Value = "PARENT"
        Sheets("result").Range("B1").Value = "CHILD"
        For Each rBom In rBomAll
            With Sheets("search")
                If rBom = Sheets("search").Range("A2") And rBom.Offset(0, 6).Value <= Sheets("search").Range("B2").Value _
                    And rBom.Offset(0, 7).Value >= Sheets("search").Range("B2").Value Then
                    If rBom.Offset(0, 2).Value <> rBom.Offset(0, 0).Value Then
                        Sheets("result").Range("B" & .Rows.Count).End(xlUp).Offset(1, 0) = rBom.Offset(0, 2)
                    End If
                End If
            End With
        Next rBom
        Worksheets("result").Activate
    End With
End Sub

Code: Select all

Dim rBom As Range, rBomAll As Range, c As Range, r As Range, rChild As Range
    With Sheets("M_BOM")
        Set rBomAll = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        Set rChild = .Range("B2", .Range("B" & .Rows.Count).End(xlUp))
        Sheets("result").Range("A2") = Sheets("search").Range("A2")
        Sheets("result").Range("A1").Value = "PARENT"
        Sheets("result").Range("B1").Value = "CHILD"

        For Each rBom In rBomAll
            With Sheets("search")
                    If rBom = Sheets("search").Range("A2") And rBom.Offset(0, 6).Value <= Sheets("search").Range("B2").Value _
                    And rBom.Offset(0, 7).Value >= Sheets("search").Range("B2").Value And rBom.Offset(0, 0).Value <> rBom.Offset(0, 2).Value Then
                     Sheets("result").Range("B" & .Rows.Count).End(xlUp).Offset(1, 0) = rBom.Offset(0, 2)
                   End If
            End With
       
            Next rBom
        Worksheets("result").Activate
    End With

Code: Select all

'Other code
For Each rBom In rBomAll
    With Sheets("search")
            If rBom = Sheets("search").Range("A2") And rBom.Offset(0, 6).Value <= Sheets("search").Range("B2").Value _
            And rBom.Offset(0, 7).Value >= Sheets("search").Range("B2").Value And rBom.Value <> rBom.Offset(0, 2).Value _
            And Application.CountIf(rBomAll, rBom.Offset(0, 2)) = 0 Then
                Sheets("result").Range("B" & .Rows.Count).End(xlUp).Offset(1, 0) = rBom.Offset(0, 2)
           End If
    End With
Next rBom

'Other code

Code: Select all

Dim intCount As Integer

 intCount = 2
   Do While Sheets("M_BOM").Cells(intCount, 1).Value <> ""
      If Sheets("search").Cells(intCount, 1).Value = Sheets("M_BOM").Cells(intCount, 1).Value Then
         Sheets("result").Cells(intCount, 1).Value = Sheets("search").Cells(intCount, 1).Value
         Sheets("result").Cells(intCount, 2).End(xlUp).Offset(1, 0) = Sheets("M_BOM").Cells(intCount, 3).Value
      
      End If
      intCount = intCount + 1
  Loop

Code: Select all

intCount = 2
Do While Sheets("M_BOM").Cells(intCount, 1).Value <> ""
    If Sheets("search").Cells(2, 1).Value = Sheets("M_BOM").Cells(intCount, 1).Value Then
        If Sheets("result").Cells(2, 1) = "" Then
            Sheets("result").Cells(intCount, 1).Value = Sheets("search").Cells(2, 1).Value
        End If
    Sheets("result").Cells(intCount, 2).End(xlUp).Offset(1, 0) = Sheets("M_BOM").Cells(intCount, 3).Value
    End If
    intCount = intCount + 1
Loop

Code: Select all

  Do While Sheets("M_BOM").Cells(ma3, "A") <> ""
                 parentR = Sheets("result").Range("A3").Value
                 If Sheets("M_BOM").Cells(ma3, "A") = parentR Then
                    parent2 = Sheets("M_BOM").Cells(ma3, "A")
                    child2 = Sheets("M_BOM").Cells(ma3, "C")
                    slotP = Sheets("M_BOM").Cells(ma3, "G")
                    elotP = Sheets("M_BOM").Cells(ma3, "H")
          
 
   If lot >= slotP And lot <= elotP Then
      ma2 = 2
      Find2 = 0
      
      Do While Sheets("result").Cells(ma2, "A") <> ""
           If Sheets("result").Cells(ma2, "A") = child2 Then
           Find2 = 1
           Exit Do
           End If
           ma2 = ma2 + 1
           Loop
   
           If Find2 = 1 Then
             Sheets("result").Cells(ra2, "A") = child2
            ra2 = ra2 + 1
         Else
            Sheets("result").Cells(rb, "B") = child2
            Sheets("result").Cells(rb, "C") = "(" & parent2 & ")"
            Sheets("result").Cells(rb, "D") = slotP
            Sheets("result").Cells(rb, "E") = elotP
            rb = rb + 1
             End If
             End If
         End If
ma3 = ma3 + 1
Loop

Code: Select all

Option Explicit

Sub Button2_Click()
    Dim Find, item, parent2, child2, ra, ra2, rb, y, lot, slotP, i ' declare i
    Dim elotP, r, re, re1, re2, ma1, ma2, parentR As Range 'declare parentR as range
    item = Sheets("search").Range("A2").Value
    lot = Sheets("search").Range("B2").Value
    r = 2
    re = 1
    re1 = 2
    re2 = 1
    ma1 = 2
    i = 0 ' assign value to i
    Set parentR = Sheets("result").Range("A2") 'set object to parentR
    Do While parentR.Offset(i, 0).Value <> ""
        Do While Sheets("M_BOM").Cells(ma1, "A") <> ""
            If Sheets("M_BOM").Cells(ma1, "A") = parentR Then
                parent2 = Sheets("M_BOM").Cells(ma1, "A")
                child2 = Sheets("M_BOM").Cells(ma1, "C")
                slotP = Sheets("M_BOM").Cells(ma1, "G")
                elotP = Sheets("M_BOM").Cells(ma1, "H")
                If lot >= slotP And lot <= elotP Then
                    ma2 = 1
                    Find = 0
                    Do While Sheets("result").Cells(ma2, "A") <> ""
                        If Sheets("result").Cells(ma2, "A") = child2 Then
                            Find = 1
                            Exit Do
                        End If
                        ma2 = ma2 + 1
                    Loop
                    If Find = 1 Then
                        Sheets("result").Cells(re2, "F") = child2
                        re2 = re2 + 1
                    Else
                        Sheets("result").Cells(re, "G") = "(" & parent2 & ") "
                        Sheets("result").Cells(re, "H") = child2
                        Sheets("result").Cells(re, "I") = slotP
                        Sheets("result").Cells(re, "J") = elotP
                        re = re + 1
                    End If
                End If
            End If
            ma1 = ma1 + 1
        Loop
        i = i + 1
    Loop
End Sub

Code: Select all

 i = 0 ' assign value to i
    Set parentR = Sheets("result").Range("A2") 'set object to parentR
    Do While parentR.Offset(i, 0).Value <> ""
        i = i + 1
    Loop