make another loop outside

[elmnas]

Hello people,

I made following code,

this checks for a couple statements,

its a Loop and in inside the loop its a IF statement.

I want to use the value (myCorrectCell) i received inside the IF statement in a new loop, cause in this loop I want to loop through all cells in column A again without any conditions/statements.

Here is my code:

For I = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
        If Cells(I, "H").Value = "MLY" And Cells(I, "i").Value = "MLY" Then
        
        mycell = Cells(I, "A").Value
        myInstances = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell)
       
        Cells(I, "B").Value = myInstances - 1
        mycount = myInstances - 1


       MyCorrectCell = Cells(I, "A").Value 'HERE IS THE VALUE I WANT TO USE OUTSIDE IN A NEW LOOP
     


        End If
  
   Next I
 'I GUESS i HAVE TO MAKE THE LOOP HERE BUT I DON'T GET IT WORK SEE MY TESTCODE HERE,[/COLOR]

For x = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

msgbox myCorrectCell ' I JUST ALERT AN EMPTY BOX WHY?

next x

End Sub

Could someone help me?

Thank you in advance

[pike]

hello
maybe

For I = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 
     
    If Cells(I, "H").Value = "MLY" And Cells(I, "i").Value = "MLY" Then 
         
        mycell = Cells(I, "A").Value 
        myInstances = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell) 
         
        Cells(I, "B").Value = myInstances - 1 
        mycount = myInstances - 1 
         
         
        MyCorrectCell =MyCorrectCell & iif(MyCorrectCell="","","~") & Cells(I, "A").Value 'HERE IS THE VALUE I WANT TO USE OUTSIDE IN A NEW LOOP
         
         
         
    End If 
     
Next I 
 'I GUESS i HAVE TO MAKE THE LOOP HERE BUT I DON'T GET IT WORK SEE MY TESTCODE HERE,[/COLOR]
 
For each x in split(myCorrectCell,"~") 
     
    msgbox x  ' I JUST ALERT AN EMPTY BOX WHY?
     
Next x

[elmnas]

Hmm..
I dont understand you function its not what I am asking for.

[elmnas]

Here is a updated code but stops I dont understand why...

Sub mytest()


For I = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
     
    If Cells(I, "H").Value = "MLY" And Cells(I, "i").Value = "MLY" Then
         
        mycell = Cells(I, "A").Value
        myOcc = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell)
         
      
        myNumOfOC = myOcc - 1
        Cells(I, "B").Value = myNumOfOC
        myOrderNr = Cells(I, "A").Value
        myPrelCost = Cells(I, "AB").Value
        
        myResult = myPrelCost / myNumOfOC
        If myNumOfOC > 0 Then GoTo Found
           
      End If
    
Found:


For X = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row


    If Cells(X, "A").Value = myOrderNr Then
    Cells(X, "A").Select
      MsgBox myResult
    End If
      
Next X


 Next I


End Sub

[pike]

Can you explain what you are trying to achieve

[elmnas]

here is my sheet original and here is my result

original.jpg result.jpg
So there is a couple statements I need to have,

IF column "H" and "I" same row have value "MLY" same row, then take the number in column A same row 35565 see how many instances it is of the number -1.
then take the value on same row (MLY) but column AB "88", divided by how many instances (in this example 88 /11 =8 (cause 35565 is 12 -1 times)
then autofil the result in all instance cells of the number

Note the MLY row can be anyway so it can be like:

Column A Column H Column I
35565 SWE DAN
35565 MLY MLY
35565 ENG ESP

[pike]

can you attach the workbook?

[elmnas]

already solved it

Sub CheckInstandCol()


  For Z = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        If Cells(Z, "H").Value = "MLY" And Cells(Z, "I").Value = "MLY" Then
        
        mycell = Cells(Z, "A").Value
        myOcc = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell)
        myNumofOC = myOcc - 1
        Cells(Z, "B").Value = myNumofOC
        myOrderNr = Cells(Z, "A").Value
        myPrelCost = Cells(Z, "AB").Value
        myResult = myPrelCost / myNumofOC
        Cells(Z, "B").Value = myNumofOC
        Cells(Z, "B").EntireRow.Interior.ColorIndex = 4
        End If
            Next Z
            
        For X = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        If Cells(X, "H").Value = "IND" And Cells(X, "I").Value = "IND" Then
        
        mycell = Cells(X, "A").Value
        myOcc = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell)
        myNumofOC = myOcc - 1
        Cells(X, "B").Value = myNumofOC
        myOrderNr = Cells(X, "A").Value
        myPrelCost = Cells(X, "AB").Value
        myResult = myPrelCost / myNumofOC
        Cells(X, "B").Value = myNumofOC
        Cells(X, "B").EntireRow.Interior.ColorIndex = 6
        End If
            Next X
            
            


End Sub




Sub CheckNumOfIns()


  For Z = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        If Cells(Z, "H").Value = "MLY" And Cells(Z, "I").Value = "MLY" Then
        
        mycell = Cells(Z, "A").Value
        myOcc = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), mycell)
        myNumofOC = myOcc - 1
        Cells(Z, "B").Value = myNumofOC
        myOrderNr = Cells(Z, "A").Value
        myPrelCost = Cells(Z, "AB").Value
        myResult = myPrelCost / myNumofOC


        Cells(Z, "B").Value = myNumofOC
     
        End If
    Next Z
    
    
    
    For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
        If Cells(i, "b").Value > 0 Then
        mycell = Cells(i, "A").Value
        myVal = Cells(i, "B").Value
        myPrel = Cells(i, "AB").Value
        mySum = Val(myPrel / myVal)
        End If
        
            For X = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
                If Cells(X, "A").Value = mycell Then
                Cells(X, "R").Value = mySum
                End If
            Next X
    
    Next i
    
End Sub