VBA to insert a formula into a cell

[envisioning]

Hello,

I have been trying to figure this out on my own, but can't seem to get it right.

I have a few ranges. Example:

Div Range1 as Range, Range2 as Range, Range3 as Range

Range1 = A1
Range2 = D1
Range3 = G1

I want G1 = A1/D1 (G1 is equal to A1 divided by D1)

However, I want to do it with VBA using the three references. Also, I don't just want the number (the quotient answer), I need the formula in the cell so that it can be used to autofill.

I tried: Range3.Range("A1").FormulaR1C1 = Range1 / Range2

but this does not work and forms an error.

I need the cell to contain the formula (A1/D1) but by using the ranges. I am doing this so I can auto-fill this formula down a column (by a macro).

Any help on this is greatly appreciated!

Thanks!

Just a note:

I know how to do this by hand, I simplified the problem to make the answer easier. Range1 and Range2 will not always be the same cell (it's a rolling average spreadsheet.

Thanks again!

 

[Greg Truby]

• It's DIM (not Div).
  
• Please use CODE tags when posting code.
  
• Please look at the VBA help file regarding the SET statement. Your code as written would attempt to assign the values of variables A1, D1 & G1 to the values of the cells you list - (which would actually be NOTHING based on what you show thus far)
  

  Range1 = A1
  Range2 = D1
  Range3 = G1
  '// should be changed to something like
  Set Range1 = Range("A1")
  Set Range1 = Range("D1")
  Set Range1 = Range("G1")

• Once you understand how to SET object references, use the macro recorder while manually entering the formula and then attempt to edit the results.

 

[envisioning]

Oops Sorry

Sorry for the confusion. I was quickly typing the code and did it incorrectly.

Sub I_Am_Cool()
 
Dim Range0 As Range, Range1 As Range, Range2 As Range, scol As Integer, srow As Integer
srow = 1
scol = 1
    Do While (Not IsEmpty(Cells(srow, scol)))
      scol = scol + 1
    Loop
Set Range1 = Cells(srow, scol).Offset(2, -1)
Set Range0 = Range1.Offset(0, 4)
Set Range2 = Range1.Offset(3, 3)
Range2.Formula = Range1 / Range0
 
 
End Sub

When I do this formula, it just gives me the answer in numerical format. Also, if it was division by 0 (it will happen in the report), it gives a debug error.

Is there a way for this to insert the formula in R1C1 or Regular Reference format by using these ranges? I need the formula to be present in Range2, not just the quotient.

Thanks!

 

[Greg Truby]

Re: Oops Sorry

Go to a cell that would need the formula you need and turn on the macro recorder and the manually enter the correct formula. Stop recording and inspect what the recorder generated.

Can you now use what this will have taught you to edit this line:

Range2.Formula = Range1 / Range0

to be what you need?