0

I am trying to create a macro in excel which does a bunch of basic calculations (multiplication, division, etc.) using columns from a table in Excel. My table looks like this:

enter image description here

The problem I have is that, as part of the calculations, the user needs to specify a constant "a" that will be used to obtain the values of a column named "Z2"with this expression: values in "Z2" = Values in column "X1"+ Values in column "Y2" multiplied by constant 'a'. In order to achieve this I tried with the following code:

Sub Macro_test()

Dim constant As Double
constant = InputBox("Insert constant 'a'")
Range("D1").Select
ActiveCell.FormulaR1C1 = "X1"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=[@X]*SIN[@Y]"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Y2"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=[@X]*[@Y]*[@X1]"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Z2"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=[@X1]+[@Y2]*constant"

Everything works well but the operation that involves the constant does not work. Any idea on what am I doing wrong?. Thanks in advance for the help.

Daniel Aviles
  • 53
  • 1
  • 1
  • 8
  • 1
    You need to concatenate it in with `&` - see the linked duplicate. – BigBen May 19 '20 at 13:39
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 19 '20 at 13:47
  • Hi @BigBen I tried with ActiveCell.FormulaR1C1 = "=[@X1]+[@Y2]*" & constant & but it does not work. What am I missing? – Daniel Aviles May 19 '20 at 14:57
  • Is your setup still the same (the table headers)? – BigBen May 19 '20 at 15:02
  • Yes indeed, the table content will vary with time but the headers will be the same. – Daniel Aviles May 19 '20 at 15:23
  • Hi @BigBen I found that I can store my variable in a cell inside my worksheet, run the calculations and then delete the cell value. Do you have any suggestion using "&" as you said? – Daniel Aviles May 20 '20 at 14:45
  • I can't repro the error... works for me, if the table headers are exactly as in the screenshot. – BigBen May 20 '20 at 14:46
  • Hi @BigBen wich expression are you using? is this one: ActiveCell.FormulaR1C1 = "=[@X1]+[@Y2]*" & constant & ? with * inside the " " ? – Daniel Aviles May 21 '20 at 15:33
  • 1
    `ActiveCell.Formula = "=[@X1]+[@Y2]*" & constant`. – BigBen May 21 '20 at 15:36
  • 1
    Hej @BigBen man I was adding (without need) an extra & at the end of my statement. That's why I was getting an error!. Now I fixed it and it is working. Thanks man. – Daniel Aviles May 21 '20 at 17:37

0 Answers0