Solved: Changing code (connecting range to dropdown selection)

[Anomandaris]

Hi guys,
Xld helped me get this code yesterday, I'm trying to modify it slightly.
2 things.
1. The calculation of the spread is slow. It worked really well for the data set I provided, but my actual datasets are 64 columns by 500+ rows each...so excel freezes for abt 3-5 mins while running it. Is there another way to calculate faster?

2. Xld(im hoping you're looking at it as you're familiar with this now), in the workbook you solved previously there were 4 markets UK,US, JPN, EUR on different sheets, and each sheet had 5 datasets- for A,B,C,D,E. I've chnaged that a bit now to suit my real sheet as I cant fit many 64 by 500 matrices in one page and its too much clutter.
So now each page has 2 datasets one Fixed range and one variable .. Alternative A (is fixed dataset) remains the same....the rest (variable dataset) i will call ' 1m, 3m, 6m, 1y' but only one will be displayed and that will depend on a cell in that worksheet...say cell B3.

1m = 21
3m = 63
6m = 126
1y = 252

So when I make my selection on sheet 'chart' lets say US 1m v EUR 3m, then macro will insert '21' into cell B3 of the sheet 'ROPE' containing US data, and insert '63' into B3 of sheet with EUR data which is 'HOOD'....see when this value is inserted, it will change the dataset, i have formulae set up on the whole range which is dependent on this cell 'B3'.


is something like this possible? my concern was that we are getting 4 different selections into one visible range...

Or is it better to have 2 options 'Fixed' and 'Variable' for selecting a named range ...and if 'Variable is selected then to choose from a set of option buttons where we can select whether its 1m,3m,6m,1y...and all this will do is insert the value into cell B3?


So thats all, this reduces visible data sets from 20 to 8.....
i hope i was clear, if not please let me know and i'll clarify
thanks

[Anomandaris]

and this is the current code


[VBA]

Public Sub GetSpread()
Dim wsChart As Worksheet
Dim wsSpread As Worksheet
Dim wsDetail1 As Worksheet
Dim wsDetail2 As Worksheet
Dim Spread1 As String
Dim Spread2 As String
Dim i As Long, j As Long
Set wsChart = Worksheets("chart")
Set wsSpread = Worksheets("Dataforchart")

Select Case wsChart.Range("Spread1_1").Value

Case "JPN": Set wsDetail1 = Worksheets("BOLT")
Case "EUR": Set wsDetail1 = Worksheets("HOOD")
Case "UK": Set wsDetail1 = Worksheets("GUN")
Case "US": Set wsDetail1 = Worksheets("Rope")
End Select

Select Case wsChart.Range("Spread2_1").Value

Case "JPN": Set wsDetail2 = Worksheets("BOLT")
Case "EUR": Set wsDetail2 = Worksheets("HOOD")
Case "UK": Set wsDetail2 = Worksheets("GUN")
Case "US": Set wsDetail2 = Worksheets("Rope")
End Select

Spread1 = wsChart.Range("Spread1_1").Value & wsChart.Range("Spread1_2").Value
Spread2 = wsChart.Range("Spread2_1").Value & wsChart.Range("Spread2_2").Value

With wsDetail1.Range(Spread1)

For i = 1 To .Rows.Count

wsSpread.Range("data").Cells(i, 1) = .Cells(i, 1).Offset(0, -1).Value
For j = 1 To .Columns.Count

If .Cells(i, j).Value <> "" And _
wsDetail2.Range(Spread2).Cells(i, j).Value <> "" Then

wsSpread.Range("data").Cells(i, j + 1) = .Cells(i, j).Value - _
IIf(Spread1 <> Spread2, wsDetail2.Range(Spread2).Cells(i, j).Value, 0)
Else

wsSpread.Range("data").Cells(i, j + 1) = ""
End If
Next j
Next i
End With

wsSpread.Select
End Sub


[/VBA]

[Mr.G]

Just a guess...

Screenupdating = False
Screenupdating = True

??

[mdmackillop]

I'm seeing Spread1 = EUR3m and Spread2 = Jpn6m, neither of which is a valid range name for With wsDetail1.Range(Spread1) etc.

[Anomandaris]

ah yes mdmac....i forgot to set the new ranges,

I've done that now....So on each page 4 names refer to the same range...eg US1m,US3m,US6m,US1y ....all refer to the second table of data..which will change depending on value in cell B3

any thoughts on what the code should look like?

and what about the spread calculation, can that be done faster?

[Anomandaris]

here's the new file

[Anomandaris]

hey Mr. G, i tried the screen updating but it only calculates the first row when i do that

I used the following at the start of the code

[VBA]
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
[/VBA]

The at the end of the code

[VBA]
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
[/VBA]

[mdmackillop]

For a good resource in speeding up code, have a look here

[Mr.G]

I am just guessing.........

[VBA]Public Sub GetSpread()
Application.Screenupdating = False
ect.......ect.......[/VBA]

And then
[VBA]Ect.......ect.......
Application.Screenupdating = True
End Sub[/VBA]

[Anomandaris]

thanks guys, but its still slow at calculating

[mdmackillop]

[vba]
Option Explicit
Option Base 1

Public Sub GetSpread()
Dim wsChart As Worksheet
Dim wsSpread As Worksheet
Dim wsDetail1 As Worksheet
Dim wsDetail2 As Worksheet
Dim Spread1 As String
Dim Spread2 As String
Dim i As Long, j As Long
Dim tim
Dim Arr()
tim = Timer

Set wsChart = Worksheets("chart")
Set wsSpread = Worksheets("Dataforchart")

Select Case wsChart.Range("Spread1_1").Value
Case "JPN": Set wsDetail1 = Worksheets("BOLT")
Case "EUR": Set wsDetail1 = Worksheets("HOOD")
Case "UK": Set wsDetail1 = Worksheets("GUN")
Case "US": Set wsDetail1 = Worksheets("Rope")
End Select

Select Case wsChart.Range("Spread2_1").Value
Case "JPN": Set wsDetail2 = Worksheets("BOLT")
Case "EUR": Set wsDetail2 = Worksheets("HOOD")
Case "UK": Set wsDetail2 = Worksheets("GUN")
Case "US": Set wsDetail2 = Worksheets("Rope")
End Select

Spread1 = wsChart.Range("Spread1_1").Value & wsChart.Range("Spread1_2").Value
Spread2 = wsChart.Range("Spread2_1").Value & wsChart.Range("Spread2_2").Value

With wsDetail1.Range(Spread1)
ReDim Arr(.Rows.Count, .Columns.Count)
End With

With wsDetail1.Range(Spread1)
For i = 1 To .Rows.Count
wsSpread.Range("data").Cells(i, 1) = .Cells(i, 1).Offset(0, -1).Value
For j = 1 To .Columns.Count
If .Cells(i, j).Value <> "" And _
wsDetail2.Range(Spread2).Cells(i, j).Value <> "" Then
Arr(i, j) = .Cells(i, j).Value - IIf(Spread1 <> Spread2, wsDetail2.Range(Spread2).Cells(i, j).Value, 0)
Else
Arr(i, j) = ""
End If
Next j
Next i
End With
wsSpread.Range("data").Offset(, 1).Resize(i - 1, j - 1) = Arr
wsSpread.Select
MsgBox Timer - tim
End Sub
[/vba]

[Anomandaris]

thats good mdmac much faster!
thanks

any idea about my 2nd question in the original post? About inserting values into cell B3 conditional on selection

[mdmackillop]

using formula

[Anomandaris]

thats brilliant!!! should work, I'll try it on monday at work and let you know...thanks a lot man

[Anomandaris]

thanks mdmac it works, I'll mark this as solved.
One thing though, it became slow again for some weird reason, was going fast on friday, dont know what happened