OMG. I tried the #3 you said and it almost worked!
Only by accident of implementation. On second thought, the test "If x Is Nothing" should be superfluous inside the loop. See the explanation below.
I tried the statement linha=x.row inside and outside the loop and it is the same. It only paints the first occurrence.
I intended that
only that statement should be inside the loop.
You also pulled "primeiracelula = x.Address" inside the loop. That is why the loop stops after only one iteration. But again, only by accident of implementation.
Bear in mind that I am not attempting to understand your design and the intent of your code. Any suggestions that I make are wild guesses based on general programming ideas. They might be wrong for your particular intent.
That said, try the following. The changes are explained below.
Code:
Dim g As Long
Dim k As Long
[COLOR=#ff0000]Dim y As Long, e As Long, lindha As Long, soma As Long
Dim primeiracelula As String
Dim x As Range, xRange As Range
Dim xKey As Variant[/COLOR]
For e = 8 To y
[COLOR=#ff0000]xKey = fs.Range("A" & e).Value
Set xRange = cs.Range("F:F")[/COLOR]
Set x = xRange.Find(xKey)
If Not x Is Nothing Then
[COLOR=#ff0000]primeiracelula = x.Address[/COLOR]
Do
[COLOR=#ff0000]linha = x.Row[/COLOR]
k = Sheets("Crono").Range("E" & linha).Value - Sheets("Crono").Range("D" & linha).Value
g = fs.Range("6:6").Find(cs.Cells(linha, 4).Value).Column
soma = g + k
With Sheets("Final").Range([COLOR=#ff0000]cs.[/COLOR]Cells(e, g), [COLOR=#ff0000]cs.[/COLOR]Cells(e, soma))
.Interior.Color = RGB(255, 153, 204)
End With
Set x =[COLOR=#ff0000] xRange.Find(xKey[/COLOR], x)
Loop While x.Address <> primeiracelula
End If
Next e
Corrections....
1. The critical correction is the use of Find(xKey,x) instead of FindNext(x).
The problem is: FindNext uses the search key ("what") of the most recent Find. In your implementation, that is cs.Cells(linha,4),
not fs.Range("A" & e).Value, as I presume you intend.
2. Note the positions of the statements "primeiracelula=..." and "linha=...".
It is essential that the first statement is outside the loop. Its purpose is to remember the first x.Address found, so we know when to terminate the Find loop.
In contrast, I presume that linha should change for each loop iteration. That is something only you can say for sure. I might be wrong. But if linha does not change each iteration, I believe "g=...Find..." will always be the same column number.
3. Note the use of
cs.Cells in the With statement.
That is a wild guess on my part. But I think
some worksheet qualifier is prudent, if not required.
Potentially "required" because without it, Cells refers to the ActiveSheet in a normal module and to Me in a worksheet object.
If either one is your intention, it is "prudent" (good programming practice) to make that explicit in the implementation, given that you reference so many other worksheets in the code fragment. It just makes you intention more clear.
And if neither is your intention, it might work now only by accident because the intended worksheet is the active worksheet during your testing.
Optimizations....
4. Note that there should be no need to test "If x Is Nothing" at the end of the loop.
The second xRange.Find should always succeed because it should wrap around to the first xRange.Find, which you ensure succeeded due to the "If Not x Is Nothing" statement.
The only reason that it was failing previously is because it was searching for the wrong key due to the use of FindNext after the fs.Range("6:6").Find statement.
5. The use of xKey and xRange is optional.
PS.... In your example, the With block serves no useful purpose since you only reference one sub-object. I presume that the example is simplified, and in the full implementation, there is more code inside the With block.
