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

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

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

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

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

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

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

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

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

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.

--
Regards,
Tom Ogilvy

"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