K
kingpin2502
Hi All
If I've neglected any forum rules, I apologise in advance. I've hit o
a problem but I must admit I'm struggling to know where to start
Although, I've had some experience with Excel VBA, unfortunately,
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 ro
contains my currencies. Column 1 contains my day range. C2R2 contain
my values. The general idea is to check whether entries exist for a da
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 valu
exists for the day range been checked then treat that cell like a
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 result
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, i
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.
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 EUR
under Spot, I shift by 1 and then print 0 for the rest of the rows i
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 sam
logic.
In the next range, 7D, there's no value specifed for the GBP(New
Formatting might not show this) so I shift by 0, but as one exists fo
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'v
written a similar macro that iterates through each row in a column an
printing the values of the cell and when it reaches the end, moves t
the next column but this one has me stumped. I can place a sample o
that code if it will be useful.
Sorry for the long winded explanation but I've found this the only wa
to represent the problem.
Regard
If I've neglected any forum rules, I apologise in advance. I've hit o
a problem but I must admit I'm struggling to know where to start
Although, I've had some experience with Excel VBA, unfortunately,
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 ro
contains my currencies. Column 1 contains my day range. C2R2 contain
my values. The general idea is to check whether entries exist for a da
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 valu
exists for the day range been checked then treat that cell like a
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 result
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, i
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.
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 EUR
under Spot, I shift by 1 and then print 0 for the rest of the rows i
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 sam
logic.
In the next range, 7D, there's no value specifed for the GBP(New
Formatting might not show this) so I shift by 0, but as one exists fo
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'v
written a similar macro that iterates through each row in a column an
printing the values of the cell and when it reaches the end, moves t
the next column but this one has me stumped. I can place a sample o
that code if it will be useful.
Sorry for the long winded explanation but I've found this the only wa
to represent the problem.
Regard