Using .Formula with variable in cells object

3
votes

I've tried a variety of concats and uses of "" but I cant get .formula to work in the following code. The macro runs without error but it does not populate my sheet. I feel like this: Excel VBA formula with variables is what I am going for but main_row does not appear to be getting assigned to the variable when it is inside the .formula. 

main_row = main.Range("b6").Row
calc_col = main.Range("k6").Column

main.Cells(main_row, calc_col).Formula = " = j & main_row / i & main_row  "
  1. Is it possible to use .formula with cells object?
  2. Would rather not use application.worksheetfunction because i want end user to see the formula
  3. Anyone have a good source to explain the proper way to qualify variables within .formula?
1
vbaexcel

1 Answers

3
votes

Try the below, I believe you were nearly there, but you inserting an unnecessary space before the equals sign + the quotes were out. 

main.Cells(main_row, calc_col).Formula = "= J" & main_row & " / I" & main_row