Thanks a lot, this is a great help!
The i was something I included to run the macro over multiple sheets, but
since it took such a long time to process one sheet, I took the i out of the
code to speed up processing temporarily. Your comments help me to grasp the
hows and whys of VBA, many thanks again!


"K Dales" wrote:

Afraid I don't have time to work out the code entirely, but here are a few
things:
1) Why the For i = 5 to 10? i is never used in the code, so this only has
the effect of making the code run 5 times over. Perhaps there is a reason
but it appears redundent.
2) Take out the Sheets().Select and Cells().Activate statements. They are
unnecessary since your code can find the cell values without the cursor
needing to be positioned there. It will also cause a lot of screen redraws
which will likely flicker (have you noted?) and takes valuable time. In
fact, set Application.ScreenUpdating = False at the top of your code and then
(IMPORTANT!) set it True again at the end to prevent any screen redraws that
can take time.
3) Instead of the loops, look at using the Range().Find and .FindNext
methods, e.g.:
Set FoundCell = Sheets(ActualMonth).Range("B:B").Find(LookupAcc.Va lue)
(Note: find can work either upwards - xlFindNext - or downwards -
xlFindPrevious. If not found, FoundCell will be Nothing. Check help from
Excel VBA for info on Find Method). As a built-in method Find will be more
efficient than looping.

So the basic outline:
1) Turn off screen updating
2) Eliminate the For i = 5 to 10 loop if you can
3) Use Find instead of loops to find the values you want
4) Find will return the cell where the value was found - use .Row and
.Column to find the row and column numbers, if needed, or just use the
.Offset method to read/write into surrounding cells.
5) Turn screenupdating back on
--
- K Dales


"PhilipsBernard" wrote:

Hi!

I'm kinda new to VBA programming, so maybe you can help me...

When I run the following macro, the program seems to crash (doesn't
respond). Is there a fault in the code or is there another explanation? All
advice welcome!

Sub FindActuals()



Actualmonth = Worksheets("COOMonthWCYTD").Range("J5")

'On Error GoTo codeBreak
'For i = 5 To 10
For LookupRow = 3 To Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set LookupAcc = Sheets(10).Cells(LookupRow, 2)
For rwIndex = 3 To 505
With Sheets(Actualmonth).Cells(rwIndex, 2)
Sheets(Actualmonth).Select
Sheets(Actualmonth).Cells(rwIndex, 2).Activate
If LookupAcc.Value = ActiveCell.Value Then
OffsetCount = -1
If Not ActiveCell.Offset(OffsetCount, -1) =
ActiveCell.Offset _(0, -1) - 1 Then

If LookupAcc.Offset(0, -1).Value =
ActiveCell.Offset _(OffsetCount, 0).Value Then
LookupAcc.Offset(0, 5).Value =
ActiveCell.Offset _(OffsetCount, 2).Value

Else: OffsetCount = OffsetCount - 1

End If
End If
End If
End With
Next rwIndex
Next LookupRow
'Next i
'codeBreak: Exit Sub
End Sub