Find the nearest point to 1000 rows where "amounts" balance and apply special formatting to that row

[Paul]

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

 

[Bernie Deitrick]

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]

Hi Bernie,

Thanks for the reply.

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

Thanks Again

Paul

 

[Bernie Deitrick]

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

 

[Tom Ogilvy]

Set a variable to hold the latest row number where the cumulative sum adds
to zero. So each time the cumulative sum hits zero, set this variable to
that row. Then when you get the the 1000th row, write rows 1 through the
number of the row held in that variable. Then adjust your row counter to
account for the rows not written.