Creating Formulas using BV

[Straightpool]

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

 

[JLGWhiz]

If ActiveCell.Value > "" Then
ActiveCell = ActiveCell.Offset(0, 1).Value/ActiveCell.Value
End If

Restated:

Dim rng As Range
rng = ActiveCell
If rng.Value > "" Then
rng = rng.Offset(0, 1).Value/rng.Value
End If

 

[Dave Peterson]

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]

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

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.

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