Hi All

If I've neglected any forum rules, I apologise in advance. I've hit on
a problem but I must admit I'm struggling to know where to start.
Although, I've had some experience with Excel VBA, unfortunately, I
cannot call myself an expert.

I have something similar to the structure below i my spreadsheet.

DAYRANGE GBP EUR
Spot BP000SPOT EUR0SPOT
Overnight BP000O/N EUR0O/N
7D EUR001W

I have the following cells and rows as listed above. The first row
contains my currencies. Column 1 contains my day range. C2R2 contains
my values. The general idea is to check whether entries exist for a day
range, if so, print a certain value e.g 1, and for all the other rows,
print a different value, e.g. 0. The final criteria is if no value
exists for the day range been checked then treat that cell like a 0
cell and print 0. Once all the rows in that currency have been checked,
move to the next currency.

To fully understand what is required, i'll print the expected results
below and work backwards

Spot
BP000SPOT, shift,1
BP000O/N, shift,0
BP0001W, shift,0
EUR0SPOT, shift,1
EUROO/N, shift,0
EURO1W, shift,0
Overnight
BP000SPOT, shift,0
BP000O/N, shift,1
BP0001W, shift,0
EUR0SPOT, shift,
EUROO/N, shift,1
EURO1W, shift,0
7D
BP000SPOT, shift,0
BP000O/N, shift,0
BP0001W, shift,0
EUR0SPOT, shift,
EUROO/N, shift,0
EURO1W, shift,1

For the Spot Day range, I first check if GBP has an entry for Spot, if
it does I write the following entry to a file
BP000SPOT, shift,1

For the rest of the date ranges not been checked in that currency, i.e
aren't SPOT, I print a shift value of 0.
BP000SPOT, shift,0

I then check the next currency and again if a value exists for EURO
under Spot, I shift by 1 and then print 0 for the rest of the rows in
that currency.

EUROSPOT, shift,1
EUROSPOT, shift,0

Once I've reached the last currency value, I check the next day range,
which in this case is Overnight, abbreviated as O/N. I run the same
logic.

In the next range, 7D, there's no value specifed for the GBP(News
Formatting might not show this) so I shift by 0, but as one exists for
EURO that gets shifted by 1.

BP0001W, shift,0
EURO1W, shift,1

Getting the Macro in VBA that works for this is where I am stuck. I've
written a similar macro that iterates through each row in a column and
printing the values of the cell and when it reaches the end, moves to
the next column but this one has me stumped. I can place a sample of
that code if it will be useful.

Sorry for the long winded explanation but I've found this the only way
to represent the problem.

Regards


--
kingpin2502Posted from http://www.pcreview.co.uk/ newsgroup access