Let say in A1:A1000, "Dave" have appear a total of 10 times.
How I do count the gap between the times and write
the answer using the row number in other cell of the same row.
But plse remember that "Dave" will appear more than 10 times in the
future.
e.g.
In C1=Dave
In D1=First time
In E1= Second time
and so on until the end.

Thanks
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sub macro1()
Dim c As Range
Dim rng As Range
Dim iCt As Integer

Set rng = Sheets("Sheet1").Range("A1:A1000")
For Each c In rng
If c = "Dave" Then
iCt = iCt + 1
If iCt = 1 Then Sheets("Sheet1").Cells(1, 3) = "Dave"
Sheets("Sheet1").Cells(1, 3 + iCt) = c.Row
End If
Next c
End Sub


HTH,
Merjet

merjet wrote:
*Sub macro1()
Dim c As Range
Dim rng As Range
Dim iCt As Integer

Set rng = Sheets("Sheet1").Range("A1:A1000")
For Each c In rng
If c = "Dave" Then
iCt = iCt + 1
If iCt = 1 Then Sheets("Sheet1").Cells(1, 3) = "Dave"
Sheets("Sheet1").Cells(1, 3 + iCt) = c.Row
End If
Next c
End Sub


HTH,
Merjet *

Hello Merjet,
Thanks for the above module.
Is it possible to use input box insteading of definining everything
inside the module itself, so that it will be more friendly user.

I mean if what I want to check against 3 cells within a specific range.
In this case can you modify the routine so that I can type in the input
box for the conditions in 3 cells and the range to search.

Thanks
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Is it possible to use input box insteading of definining everything
inside the module itself, so that it will be more friendly user.

Sub macro1()
Dim str1 As String
Dim str2 As String
Dim c As Range
Dim rng As Range
Dim iCt As Integer

str1 = InputBox("What do you want to search for?")
str2 = InputBox("In what range, e.g. A1:A10?")
Set rng = ActiveSheet.Range(str2)
For Each c In rng
If c = str1 Then
iCt = iCt + 1
If iCt = 1 Then ActiveSheet.Cells(1, 3) = str1
ActiveSheet.Cells(1, 3 + iCt) = c.Row
End If
Next c

End Sub


HTH,
Merjet

merjet wrote:
* Is it possible to use input box insteading of definining
everything
inside the module itself, so that it will be more friendly user.

Sub macro1()
Dim str1 As String
Dim str2 As String
Dim c As Range
Dim rng As Range
Dim iCt As Integer

str1 = InputBox("What do you want to search for?")
str2 = InputBox("In what range, e.g. A1:A10?")
Set rng = ActiveSheet.Range(str2)
For Each c In rng
If c = str1 Then
iCt = iCt + 1
If iCt = 1 Then ActiveSheet.Cells(1, 3) = str1
ActiveSheet.Cells(1, 3 + iCt) = c.Row
End If
Next c

End Sub


HTH,
Merjet *

I need to check more than one cell conditions, lets say 3 cells value
then what should I do ?

Thanks
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/