Which variable to use

BobbyJack

New Member
Joined
Jul 22, 2009
Messages
18
Hi

I am declaring this as a string but I get a debug type miss match error.

Dim ProposalYear As String

ProposalYear = Worksheets("Calculation Matrix").Range("L41:M41").Value

L41:M41 is a merged cell is this the best way to declare this.

Thanks
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
VBA hates merged cells. Much better use Center Across Selection and then just:

Code:
Dim ProposalYear As String
ProposalYear = Worksheets("Calculation Matrix").Range("L41").Value

Dom
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
I hate merged cells and avoid them like the plague! However...

What is returned if you use:

Code:
Dim ProposalYear as String
 
ProposalYear = Sheets("Calculation Matrix").Range("[B][COLOR=red]L41[/COLOR][/B]").Text
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929

ADVERTISEMENT

Merged cells are the devil's work.

Suspect you'll have to just refer to L41.

Not sure whether the String data type is particularly bomb-proof when referring to the value of a cell (and will certainly fail if the cell contains an error value).
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Not sure whether the String data type is particularly bomb-proof when referring to the value of a cell (and will certainly fail if the cell contains an error value).

Hence use .Text property instead of .Value...
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929

ADVERTISEMENT

Oh yeah, of course!

.Text returns the displayed value rather than the held value, right?
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Kinda, but rather than saying 'displayed value' I'd just say that it returns the String value of the range (when referring to the range that is)...
 

BobbyJack

New Member
Joined
Jul 22, 2009
Messages
18
Thanks everyone

I have only taken the first part of the cell "L41" and it is now working

thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top