Solved: Subtracting Named Ranges from combo box selection

[Anomandaris]

I have 4 sheets with raw data, Sheets named BOLT,HOOD,GUN,ROPE. In each of these sheets there's 5 datasets which I have set as dynamic ranges
eg. on "BOLT" you can see JPN alternatives A to E, the ranges are named JPNA,JPNB,JPNC,JPND,JPNE
similarly in "HOOD" you see EUR alternatives from A-E, ranges are named EURA,EURB and so on
GUN........has ranges UKA,UKB etc
"ROPE" has ranges USA,USB,USC, etc
Now what I'm trying to do is, when I make a selection on the sheet 'chart' from the drop down boxes, I need to get the spread(difference) between the 2 selections and copy this into the Range 'DATA' in sheet 'Dataforchart'. And if both selections are the same e.g. US- alternative B, US- alternativeB, then simply copy that range into Dataforsheet.
How do i do that, I havent used comboboxes and option buttons so im not sure how to link it to the data....
*** ...as you will see in the data on 5th May there is no data for EUR alternatives, so its left as blank...
Say for e.g. If during calculation of Range(EURA) minus Range (JPNA)
EURA has blanks on May 5th, JPNA doesnt have any blanks....so on that day I dont want any calculation done, simply show it as a blank on Dataforchart
how do i do that?
this is the code i have for subtracting: (currently on Dataforchart I've done the USA - UKB spread. Is there another way to refer to the ranges using the Name? I tried to use range names-- USA-UKB, but that didnt work)


Sub SpreadUSA_UKB()
With Worksheets("Dataforchart").Range("DATA")

.Formula = "=ROPE!C14 - GUN!M13 "
.Value = .Value
End With
End Sub



As you can see on 'Dataforchart', it gives me some '0's around the table, why is that? and how do i get rid of it?
Please see the file as without it even i wouldnt understand what i'm talking about haha

any help or advice would be greatly appreciated,

thanks a lot

[Bob Phillips]

.

[Anomandaris]

thanks a lot xld,

just one issue - the blank cells....it still performs calculation when one row is blank in a range,.....eg. EUR has a blank row May 5, JPN doesnt have any bl;ank rows....on Dataforchart I want ....May 5 to appear as blank, instead of the spread.
it distorts the data so im trying to ignore that observation(date) entirely...

[Bob Phillips]

.

[Anomandaris]

Brilliant! works the way i needed,
one question though, In your code when doing all the spreads I noticed you never referred to the ranges by name.....how does it still work?

when i make a sel;ection on 'chart' how does it know which 2 ranges to select?

thanks alot man, i'm getting to the end of this project slowly but surely thanks to you

[Anomandaris]

thats fine I understand it now