Paul,

Let's say that you have a column of integers in column A, and you want to find the numbers 15 and 16
when they are in order within column A.

Forget for a moment that this would be a terrible way to do this particular task: it is just for
illustration purposes.

Fill in column A with numbers (with or without 16 following 15 somewhere (once or more often) within
column A) and run this macro. It fills the array myArray with the values to do the processing.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myArray(1 To 20) As Integer
Dim myCount As Long
Dim myRow As Long
Dim myVals As String
Dim i As Integer

'Fill the array with the first 20 values
For i = 1 To 20
myArray(i) = Cells(i, 1).Value
Next i

For myCount = 20 To Cells(Rows.Count, 1).End(xlUp).Row Step 10
'Check for whatever it is using the first _Eleven_ values
myVals = ""
For i = 1 To 11
myVals = myVals & " " & myArray(i)
Next i
MsgBox "The numbers being processed are " & Chr(10) & myVals
If InStr(1, myVals, " 15 16") 0 Then
MsgBox "The two consecutive numbers 15 and 16 were found"
End If
'Move the Values up
For i = 1 To 10
myArray(i) = myArray(i + 10)
myArray(i + 10) = Cells(myCount + i, 1).Value
Next i
Next myCount

'Process the last set of numbers
myVals = ""
For i = 1 To 11
myVals = myVals & " " & myArray(i)
Next i
MsgBox "The numbers being processed are " & Chr(10) & myVals
If InStr(1, myVals, " 15 16") 0 Then
MsgBox "The two consecutive numbers 15 and 16 were found"
End If

End Sub



"Paul" <paulm dot c @ iol dot ie wrote in message ...
Hi Bernie,

Thanks for the reply.

I'm not familiar with Stacks, could you give me an example?

Thanks Again

Paul


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Paul,

Use a stack or buffer: fill an array of 20 or 30 values, process them, then decide which is the
right one before writing them out to the file. If none are correct, move the values "up" the
array by some amount (say 10 steps), and refill the last 10 values, and reprocess.....

HTH,
Bernie
MS Excel MVP


"Paul" <paulm dot c @ iol dot ie wrote in message ...
Hi,

I'm writing a macro to create a csv file from a spreadsheet that has to be formatted in a
certain way.

One rule for the csv I'm getting stuck on is as follows:

The speadsheet could contain say 20000 lines. The fields could be Journal number, description,
Amount.

As I extract each row, the code should find the nearest row to 1000 where the amounts evaluate
to zero (the amounts are pluses and minuses) at which point I can apply specific fomating to
that row in the csv extract then to start the count again from 1 and find the next nearest row
to 1000 and again apply the specific formatting to that row in the csv extract.

I'm thinking that I could step through each row and add the amounts (amount = amount + amount)
but the problem is the formatting should occur at the NEAREST point to 1000 rows as possible.
How do I get the code to count back from 1000 to find that point if when I'm adding the amounts
I'm starting from 1 and counting up.

Any advise appreciated

Paul