find occurences

[elmnas]

Hello people,

I have made following code:



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


End If


Next I

End Sub

How do I find all occurrences of the string mycell in the same column

mycell value is not always the first occurrence.


Could someone help me?

[mancubus]

i am not sure i understand your requirement...



Sub vbax_53690_FindCellsBasedOnCondition()

Dim MyString As String

MyString = "Cell Value" & vbTab & "Cells Address" & vbLf

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(i, "H").Value = "MLY" And .Cells(i, "I").Value = "MLY" Then
MyString = MyString & .Cells(i, "A").Value & vbTab & .Cells(i, "A").Address(False, False) & vbLf
End If
Next i
End With

MsgBox MyString

End Sub

[elmnas]

i am not sure i understand your requirement...



Sub vbax_53690_FindCellsBasedOnCondition()

Dim MyString As String

MyString = "Cell Value" & vbTab & "Cells Address" & vbLf

With ActiveSheet
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(i, "H").Value = "MLY" And .Cells(i, "I").Value = "MLY" Then
MyString = MyString & .Cells(i, "A").Value & vbTab & .Cells(i, "A").Address(False, False) & vbLf
End If
Next i
End With

MsgBox MyString

End Sub


I dont understand what you made really?

[mancubus]

do you really understand what you have done in post #1?

[elmnas]

of course..
but can you explain what you done?

do I need to explain for you in my first post what I have done.

Thank you in advance...

[mancubus]

elmnas

if values in col H and col I are "MLY" it takes col A's values and addresses and displays the, lets say, 'result table' in MsgBox.

so multiple occurences based on H and I are identified and listed?



and there was no need to open a second thread. yo would include that loop requirement in this thread as well.

[elmnas]

already solved..

[snb]

Use autofilter.