Find Method To Find 2nd Occurence

[Ann_BBO]

[VBA]Private Sub cmdbutton1_Click()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim cel As Range
Dim Rng As Range
Dim c As Range
Dim i As Long

Application.ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Set Source = wbSource.Sheets(1).Columns(1)
With Tgt
.Activate
'clear old data
Range(.Cells(21, 2), .Cells(25, 3)).ClearContents
Range(.Cells(21, 5), .Cells(25, 5)).ClearContents

' For extract the data, change the Range value to fulfill the data structure
If Range("A21").Value = 001 Then
Range("A21").Value = "Staff 001"
End If
If Range("A22").Value = 002 Then
Range("A22").Value = "Staff 002"
End If
If Range("A23").Value = 003 Then
Range("A23").Value = "Staff 003"
End If


'Loop through names in column A
For Each cel In Range(.Cells(21, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then
Set c = Source.Find(cel)
Set Rng = Range(c.Offset(1), c.Offset(1).End(xlDown))
For i = 1 To 1
cel.Offset(, i) = Application.Average(Rng.Offset(, i + 6)) / 1000
Next
For i = 2 To 2
cel.Offset(, i) = Application.Average(Rng.Offset(, i + 6))
Next
For i = 4 To 4
cel.Offset(, i) = Application.Average(Rng.Offset(, i + 5))
Next
End If
Next
End With

'Refill the original range value
Range("A21").Value = 001
Range("A22").Value = 002
Range("A23").Value = 003

wbSource.Close False
Application.ScreenUpdating = True
End Sub[/VBA]

The above vba command (help by mdmackillop)which is extract the data from the Other workbooks. It looks for the "Staff 001", "Staff 002"...these parameters to transfer the data to the worksheet. But, the "Staff 001" data must appear twice in each workbooks. If i use the above command, i only can extract the FIRST "Staff 001" average data. But SECOND "Staff 001" average data cannot extract. I know it may be use FindNext method to do this but i am not sure how to write it.

P.S. I spend much time and try my best on this part but i can't write the unsucessful command . Hope all of you can help me!!

[Simon Lloyd]

looking at the code above it seems that you must have asked mdmackillop to check an exact cell for the occurence rather than a range, i.e you only check A21 for the occurence of 001 so naturally it will only work with that range, your calculations then follow an offset of this cell, where would the occurences of "Staff 001" be found? over the whole sheet? only in column A? a set range?, you need to make a better explanation of exactly what you need to recieve the help you require!

[Ann_BBO]

Thanks first
"Staff 001" , "Staff 002",....can be found from other workbooks. I have the series of this Workbooks. Each "Staff 001","Staff 002"... will have the own data range. Each Workbooks must contain 2 "Staff 001" Occurence.

In the worksheet, i create the excel form which have the "Staff 001", "Staff 002" name..in the range(A21 to A23). Refer to the name in the street to find the corrsponding data from the Workbooks and extract the average value. My main problem that i have no idea for extract the average data from the 2 Occurence // parameters in "Staff 001".

Now, i contain the corrsponding Workbooks file here
Attachment 6244

P.S. My english is not good, i hope you can understand my meanings. Thank you!!!

[JimmyTheHand]

My english is not good, i hope you can understand my meanings.

I hope, too

I assumed that you wanted to calculate a single average value for both Staff 001 sections, instad of two separate values (one for each section). So, I wrote something to do this. It may or may not work, I have not tested. It should, though, even with 3 or more Staff 001 sections, or more Staff 002 sections, etc.
Replace the middle part of your code with this:
[vba]
'Loop through names in column A
For Each cel In Range(.Cells(21, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then
Set c = Source.Range("A1")
Set Rng = Nothing
Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row
If c = cel Then
If Rng Is Nothing Then Set Rng = c.Offset(1)
Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown)))
Set c = c.Offset(1).End(xlDown).Offset(1)
Else
Set c = c.Offset(1)
End If

Loop
cel.Offset(, 1) = Application.Average(Rng.Offset(, 7)) / 1000
cel.Offset(, 2) = Application.Average(Rng.Offset(, 8))
cel.Offset(, 4) = Application.Average(Rng.Offset(, 9))
End If
Next[/vba] Please note the red bold lines. As far as I know, there is no point in using For..Next loop in the way you did. The cycle
[vba]For i = 4 To 4 [/vba] executes only once, and here variable i gets only one value, which is 4. So in the core of this For..Next cycle each i can be replaced by a constant 4. That is what I did to those red lines.

Jimmy

[Ann_BBO]

Thanks Jimmy

It is very useful for me~~
If you din't mind, would you explain the below vba meaning as i want to learn it.
[VBA]If Not cel = "" Then
Set c = Source.Range("A1")
Set Rng = Nothing
Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row
If c = cel Then
If Rng Is Nothing Then Set Rng = c.Offset(1)
Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown)))
Set c = c.Offset(1).End(xlDown).Offset(1)
Else
Set c = c.Offset(1)
End If

[/VBA]

[JimmyTheHand]

Okay.
Here's the part of code, with code lines numbered for reference.

1.  If Not cel = "" Then 
2.      Set c = Source.Range("A1") 
3.      Set Rng = Nothing 
4.      Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row 
5.          If c = cel Then 
6.              If Rng Is Nothing Then Set Rng = c.Offset(1) 
7.              Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown))) 
8.              Set c = c.Offset(1).End(xlDown).Offset(1) 
9.          Else 
10.             Set c = c.Offset(1) 
11.         End If
12.     Loop 
13. End If

Line #1 and #13 bracket a core code that runs only if value of cel is not an empty string.
The objective of the core code (Lines #2 through #12) is to scan Column A of Source sheet. It loops through (almost) all cells in Column A, looking for occurences of cel.Value (i.e. Staff 001, Staff 002, etc.) Looping is done by lines #4 and #12.

Now, if the current cell's value is of interest, I mean, it is Staff 001, or Staff 002, etc., then I pick the range of data below it

Range(c.Offset(1), c.Offset(1).End(xlDown)

and join this range to the previous range, that belonged to the same staff number. This joining is done by code line #7.
Example:
After finding the 1st occurence of Staff 001, the range will be "A4:A17".
After finding the 2nd occurence of Staff 001, the range will be "A4:A17,A42:A50".

After the current cell has been processed, whether it was Staff 001 or something else, the focus of attention moves to the next cell.
Line #10 just moves down by one cell. Purpose of line #8 is to skip those cells that belong to the data range of Staff 001.

The loop is finished when the current cell's rowindex (c.Row) becomes equal with the last cell's rowindex. (See line #2 for the looping condition.)

c.Row = Source.Range("A" & Source.Rows.Count).End(xlUp).Row

Code lines #2 and 3 are starting values.
Code line #6 is necessary because Union method gives an error, if one of it's arguments is Nothing, and Rng, in the beginning, is Nothing.

Of course, this problem could have been solved by using FindNext, as well. Probably it would have been more elegant. I personally don't like FindNext very much, so I try to work around.

I hope this helped you to understand.

Jimmy

[Ann_BBO]

Thank you Jimmy
The solution is vert details!!!

[Ann_BBO]

I find the problem in the worksheet.
If i refer the cells(21,1) or range(A21) to find the values from the source.worksheet, then it will search the all Column A area.
However, if i add the other parameters in the range(A28) say "Hello", then it will output the error code in vba. Therefore, if i want to search range area from cells(21,1) to cells(27,1) or Range("A21:A27"), how to modify it.

Thank you

[JimmyTheHand]

If you need a fixed range A21:A27 then the modified code line, instead of
[vba]For Each cel In Range(.Cells(21, 1), .Cells(Rows.Count, 1).End(xlUp))[/vba] will be this:
[vba]For Each cel In Range("A21:A27")[/vba]
The essence of the solution is how you define the range you are working on. The above code will surely work in one particular case, but might not work in others. It is not flexible at all. In order to write a good, flexible code, you need to be able to tell it in words, spoken or written, how you define the range. If you tell it to me, I will put it into code.

In general, the fastest way to solution is that you post sample workbooks, and supplement them with written explanation.

Jimmy

[Ann_BBO]

Thanks again Jimmy ^.^
As i am the beginners for vba, so i have many problems.
Anyway, i will try the best to learn it.

Regrads
Ann