PDA

View Full Version : Automating calculations between values on 2 separate worksheets



Visual Newby
07-25-2012, 07:30 AM
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!

Kenneth Hobs
07-25-2012, 07:56 AM
The easiest way to help is by working for a short example file. Attach one with an example of data and what you want.

Visual Newby
07-25-2012, 08:39 AM
Sure, please the the attached excel file.

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.

If anything is not clear please let me know.

Visual Newby
07-26-2012, 01:47 AM
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)

priceUSD.Cells(y, x) = stock_price / stock_fx

Loop Until y = 858



End Sub

Kenneth Hobs
07-26-2012, 05:22 AM
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.
Range("A1")
Range(cells(1,1))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.

I will look at your workbook later tonight.

Visual Newby
07-26-2012, 06:37 AM
Thanks very much Kenneth, your insight on my spreadsheet would be much appreciated. Please let me know if you need anything else