Create formula to use cell address?

[kphu]

Hey All,

I'm a VBA newbie. Was wondering if anyone can help me with this.

sub formula ()
a = ActiveCell.Address(False, False)
Selection.End(xlUp).Select
Selection.Offset(0, 1).Select
b = ActiveCell.Address(False, False)

end sub

I would like to create a formula that uses the cell address values for a and b.

lets say the a stores the cell as E140 and b as E93.

How do I get the formula to write: =E93/E140 instead of b/a

Thanks,

 

[kphu]

Hey Guys,

I figured it out.

Here's the code I got if anyone is interested:

a = Range(ActiveCell.Address).Address
Selection.End(xlUp).Select
b = Range(ActiveCell.Address).Address
Selection.Offset(0, 1).Select
ActiveCell.Formula = _
"=" & Range(b).Address(False, False) & "/" & Range(a).Address(True, False) & ""

Thanks,

 

[Zathras]

Not sure what you are really doing, but this does the same thing with slightly less code:

Sub test()
Dim r As Long
  r = ActiveCell.Row
  With ActiveCell.End(xlUp).Offset(0, 1)
    .FormulaR1C1 = "=RC[-1]/R" & r & "C[-1]"
    .Select
  End With
End Sub

And if you don't need to select the cell where the formula is going for any other purpose, you can save quite a bit of code:

Sub test()
Dim r As Long
  r = ActiveCell.Row
  ActiveCell.End(xlUp).Offset(0, 1).FormulaR1C1 = _
                "=RC[-1]/R" & r & "C[-1]"
End Sub

 

[kphu]

I left some of the coding out, but basically what it does is that it takes the 1st cell in the group and divides it by the last cell in the group. The number of cells in the group can change anytime I run the report.