Run-time error '13': Type mismatch

[rcg78]

I'm very new to VBA and am trying to create a sub that pulls stock prices from one sheet in my workbook and calculates the percentage change between the prices in another sheet. The code isn't fully built out yet. I am trying to build step by step and have run into a problem in my variables/formula for calculating percentage change. I get the error message "Run-time error '13': Type mismatch" Here is the code I have so far (with the problem being noted with * *):

Private Sub CommandButton2_Click()


'Do Calculations


Dim percentage As Double, calcRange As range, stockPrice As range, n As Currency, o As Currency


Set calcRange = Worksheets("Calculations").range("B2:W17")
Set stockPrice = Worksheets("Adjusted Close Price").range("B2:W17")


With Worksheets("Adjusted Close Price")

*o = stockPrice.Value*
n = stockPrice.Offset(, 1).Value

calcRange.Value = (n - o)/o

End With


calcRange.NumberFormat = "0.00%"


End Sub


Any help is appreciated!

 

[NdNoviceHlp]

Dim O as Double, N as Double.
Format output as currency. HTH. Dave

 

[jim may]

I only created 2 four column Ranges in each worksheet-- No VBA (Macro) needed...
Just enter formula as I did in cell B20, Format it 0.00% and copy to C20:E20; then select B20:E20 and copy down to B21:E35; Does that help?

Excel 2012
	A	B	C	D	E
1
2		289	173	162	305
3		155	252	147	169
4		377	370	383	336
5		427	392	371	316
6		238	468	294	225
7		289	303	106	118
8		340	137	240	171
9		223	193	296	258
10		388	370	328	462
11		234	345	311	154
12		380	314	169	435
13		415	188	229	354
14		397	136	237	137
15		367	429	289	292
16		185	141	360	295
17		464	295	111	110
18
19
20		-62.28%
Adjusted Close Price

Cell Formulas
Range	Formula
B20	=(B2-Calculations!B2)/B2

Excel 2012
	A	B	C	D	E
1
2		469	326	316	493
3		390	424	258	191
4		350	296	452	247
5		101	383	479	471
6		251	469	467	115
7		138	459	196	332
8		239	100	225	494
9		251	230	323	491
10		367	343	203	220
11		240	173	162	225
12		417	451	409	111
13		229	295	194	469
14		227	332	438	105
15		255	219	421	128
16		197	454	209	294
17		427	439	181	260
Calculations

 

[rcg78]

Dave,

Thanks for your response. I tried your suggestion but it didn't seem to change anything. I'm wondering if Excel is not interpreting the cells on "Adjusted Close Price" as numbers. Is there a way to tell what type of variable Excel is reading it as?

 

[rcg78]

Jim,

Thanks for your response and example. I will be updating this sheet on a regular basis, so I would prefer to automate this process with VBA. I appreciate your work though!

 

[NdNoviceHlp]

maybe start from the beginning... where's the data, what do you want to do with it, and where is the outcome supposed to go. You have set stockprice as a range so it has no value.... each item in the range has a value. Dave

 

[rcg78]

I was able to figure it out! Thanks for your help Dave!