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

14346 14347
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