Iterating through cells, comparing values

[kingpin2502]

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