Automating calculations between values on 2 separate worksheets
Hi,
I've got a workbook with a couple of worksheets. One of the worksheets contains historical data for various stock indexes. The other contains historical foreign exchange rates. I need to divide the stock index values by the relevant fx rates and produce the output in a new worksheet.
Do you know of a good way to automate these calculations?
If you need any more information please let me know, thanks in advance!
On the "data" tab I have all the tickers and their historic values.
I then have the "currency" tab, which has numerous currency values at dates corresponding to those in the data tab.
I need to create a new sheet in the workbook that contains the values in the data tab divided by the relevant currency value in the "Currency" tab.
The relevant currency is given in row 2 of the "Data" tab and the correponding column in the "currency" tab is given in row 3. For example MXWO INDEX is in USD (shown in cell B2) and the column containing the USD FX rate is column 47 in the "Currency" tab. Consequently I need to divide the values in column B of the "Data" tab by the values contained in column 47 of the "Currency" tab. This then needs to be repeated for all cells in the data tab, and the output provided in a new sheet.
I have been working on this but can't quite get it to work. I'm using option explicit and defined both X and Y as integers at the module level. However, I don't understand why when I run it, it comes up with "Run time error 91" "Object variable or With block variable not set" on the line of code shown in red below.Any help would be much appreciated here.
x = 3
Do
x = x + 1
convert_fx_usd
Loop Until x = numStocks + 4
wksPriceUSD.Range("A1").Select
End Sub
Sub convert_fx_usd()
Dim wksdata As Worksheet, wksInput As Worksheet, wksCurrency As Worksheet, priceUSD As Worksheet
Set wksdata = ThisWorkbook.Worksheets("Data")
Set wksInput = ThisWorkbook.Worksheets("Input")
Set wksCurrency = ThisWorkbook.Worksheets("Currency")
Set priceUSD = ThisWorkbook.Worksheets("Price USD")
Dim stock_fx_column As Range
Set stock_fx_column = wksdata.Cells(3, x)
y = 3
Do
y = y + 1
Dim stock_price As Range
stock_price = wksdata.Range(y, x)
Dim stock_fx As Range
Set stock_fx = wksCurrency.Range(y, stock_fx_column)
Please use VBA code tags when posting codes. Be sure to Dim row number variables as Long. Use Cells() if you are going to use number for rows and columns. Range will accept the string value in string format or range object format. e.g.
[vba]Range("A1")
Range(cells(1,1))[/vba]Of course the last example is just silly. If the second parameters is used, it is for a consecutive range of cells or it can be used as a discontinuous range area.
The other thing is that you did not "Set" stock_price.
While you do not have to, I use the property Value or Value2 for a range rather than using the default.