Thanks for the info. The RC referencing is just what I was looking for.
fwiw, I looked at the subject line of my thread and had to laugh when I saw
I had "VB" as "BV." lol
Brad
"Dave Peterson" wrote:
> One way is to look at the row that the activecell is on and use that to build
> the formula:
>
> Option Explicit
> Sub testme01()
>
> Dim myRow As Long
> Dim myFormula As String
>
> With ActiveCell
> myRow = .Row
> myFormula = "=if(isblank(J" & myRow & "),,i" _
> & myRow & "/j" & myRow & ")"
> .Formula = myFormula
> End With
>
> End Sub
>
> This works ok if I don't know what column the cell is in that's going to get the
> formula.
>
> But if I know the column, I'll use the .formulaR1c1 reference style.
>
> For instance, if this formula is going in column K, then the code gets much
> easier and looks like:
>
> Option Explicit
> Sub testme02()
> '=IF(ISBLANK(J7),,I7/J7)
>
> Dim myRow As Long
> Dim myFormulaR1C1 As String
>
> With ActiveSheet.Cells(ActiveCell.Row, "K")
> myFormulaR1C1 = "=if(isblank(rc[-1]),,rc[-2]/rc[-1])"
> .Formula = myFormulaR1C1
> End With
>
> End Sub
>
> In fact, when I have to do this, I'll write the formula in the cell to get it
> working correctly.
>
> Then I'll turn on the macro recorder and select that cell, hit F2 and enter.
> Then turn off the macro recorder.
>
> This gives me what that .formular1c1 reference style formula should be:
>
> ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),,RC[-2]/RC[-1])"
>
> R1C1 reference style can be toggled (xl2003 menus):
> tools|options|General|check or uncheck R1C1 reference style
>
> rc[-1]
> is the same row as the cell with the formula, but one column to the left.
>
>
>
>
>
> Straightpool wrote:
> >
> > I need to create a formula in a VB macro based on the ActiveCell. The formula
> > should look like the following:
> >
> > =IF(ISBLANK(J7),,I7/J7)
> >
> > I7 & J7 will change based on the current row.
> >
> > I am new using VB so any help will be greatly appreciated.
> >
> > Thanks,
> > Brad
>
> --
>
> Dave Peterson
>
|
Similar Threads
