Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Formulas using BV
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Formulas using BV
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 "Straightpool" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Formulas using BV
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Formulas using BV
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating formulas | Excel Discussion (Misc queries) | |||
Creating Formulas | Excel Worksheet Functions | |||
Creating formulas | Excel Worksheet Functions | |||
Self creating formulas | Excel Programming | |||
creating formulas in XL | Excel Programming |