0

I have a variable a = "C2" and variable b = "C3"

I need to do something like

ws.Range("A2").Value = "=" & a &"" ""& b

which should return:

=C2&" "&C3

in an excel formula however I cant seem to get this to work anyone have a solution??

Tyler Cowan
  • 820
  • 4
  • 13
  • 35
  • 2
    Are you trying to get your cell to have the formula `=C2&" "&C3`, or do you want it to show the values of C2 and C3, separated by a space? Try `ws.Range("A2").Formula = "=" & a & "&"" ""&" & b`. If you want the *values* of your variables, separated by a space, use `ws.Range("A2").Value = a & " " & b`. – BruceWayne Jan 13 '16 at 21:38
  • 1
    @BruceWayne this is the correct answer the first one worked great. Thanks! – Tyler Cowan Jan 13 '16 at 22:05

1 Answers1

2

From my comment,

ws.Range("A2").Formula = "=" & a & "&"" ""&" & b

This works because you need to use .Formula instead of .Value.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    Actually also works with .Value interestingly enough – Tyler Cowan Jan 13 '16 at 23:02
  • @TylerCowan - UUhmmm, I'm just going to ignore that then, to keep my sanity :P I *thought* I was getting the hang of VBA, and then you tell me I can do `.Value` just the same. If someone can comment as to why, that'd be appreciated! Perhaps it's because you're using an `=` at the start, so of course, when Excel sees an `=` at the start, it uses it as a formula. I wonder then, what the "real" big difference in using `.Value` and `.Formula` for setting formulas is. – BruceWayne Jan 13 '16 at 23:28
  • 1
    Imagine you have integers, doubles for certain calculations and if you use .formula you gonna get screwed. Because .FORMULA always return a String. (Any value set to .formula without is a plain value not a formula) Unless you have exception handling and the extras ready in your code. Whereas .VALUE returns your data's data type as you expect them to be. In terms of retrieving formula: Value is one way road in case to set formula but not to retrieve. http://stackoverflow.com/questions/13687800/why-use-range-formula-in-vba-for-excel-2003-instead-of-range-value – Tyler Cowan Jan 13 '16 at 23:35