Dear All

can VBA easily do this -

I have a file with 2000 transactions from an ERP solution. The transactions
are sorted. A valid transaction A has a record B below it in the file. I want
the rate from record B to replace the rate from record A. So need a way of
reading within records or at least down a record.

Record A 2.33 Code2
Record B 4.66 Code2
Record C 7.99 Code4

I want Record A to recieve 4.66 and not use 2.33. They match because they
both have Code2 as an identifier although my file is much more complex on
what counts as a match

Of course in the file I have lots of records that have no children and I am
trying to only match where I have 1:1 logic

Any help would be appreciated and if you do suggest something thanks in
advance for your time and effort. I am not an advanced VBA user so sorry if I
cannot explain this issue easily.

So far I have the code shown blow to loop through the record set

For Each thecell In Range("B1:B2000")

i = i + 1
If thecell.Value = 0 Then

' Exclude rubbish record no process

Worksheets("Sheet1").Cells(i, 50).Value = "Excluded"


Else

' may be valid based on a number of things
' which we must check


End If

Next thecell

many thanks in advance if you help
--
Chris Ward

problem now solved do not work or look at this question anymore
--
C Ward


"christopher ward" wrote:

Dear All

can VBA easily do this -

I have a file with 2000 transactions from an ERP solution. The transactions
are sorted. A valid transaction A has a record B below it in the file. I want
the rate from record B to replace the rate from record A. So need a way of
reading within records or at least down a record.

Record A 2.33 Code2
Record B 4.66 Code2
Record C 7.99 Code4

I want Record A to recieve 4.66 and not use 2.33. They match because they
both have Code2 as an identifier although my file is much more complex on
what counts as a match

Of course in the file I have lots of records that have no children and I am
trying to only match where I have 1:1 logic

Any help would be appreciated and if you do suggest something thanks in
advance for your time and effort. I am not an advanced VBA user so sorry if I
cannot explain this issue easily.

So far I have the code shown blow to loop through the record set

For Each thecell In Range("B1:B2000")

i = i + 1
If thecell.Value = 0 Then

' Exclude rubbish record no process

Worksheets("Sheet1").Cells(i, 50).Value = "Excluded"


Else

' may be valid based on a number of things
' which we must check


End If

Next thecell

many thanks in advance if you help
--
Chris Ward