Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
I want to insert a formula into a row if the first cell in the row has
something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Dim rng as Range, cell as Range
set rng = Columns(1).Specialcells(xlConstants) ' or xlformulas for each cell in rng cell.offset(0,1).formula = "=if(" & cell.Address(0,0) & _ "<"""",""Filled"",""empty"")" Next -- regards, Tom Ogilvy "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
How do i ignore the first row? I also need to be able to sum three cells in
the row. Any ideas on how to this? This is what i am using. Sub CommandButton4_Click() Dim rng As range, cell As range Set rng = Columns(1).SpecialCells(xlConstants) ' or xlformulas For Each cell In rng cell.Offset(0, 8).Formula = "=sum(9,F2:H2)" Next End Sub "Tom Ogilvy" wrote: Dim rng as Range, cell as Range set rng = Columns(1).Specialcells(xlConstants) ' or xlformulas for each cell in rng cell.offset(0,1).formula = "=if(" & cell.Address(0,0) & _ "<"""",""Filled"",""empty"")" Next -- regards, Tom Ogilvy "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Quick and dirty (using RC notation for the formula would be neater though):
============================== Private Sub CommandButton4_Click() Dim rng As Range, cell As Range Set rng = Columns(1).SpecialCells(xlConstants) Dim row As Long For Each cell In rng row = cell.row If row 1 Then cell.Offset(0, 8).Formula = _ "=sum(9,F" & row & ":H" & row & ")" End If Next cell End Sub ============================== But I can't help wondering: do you really need a macro to do this? Maybe using a macro is a bit of overkill for this particular problem...? Couldn't you use a normal Excel formula instead, and copy it to the cells you want to fill in? What about putting this into cell I9: =IF($A2="";"";SUM(9;$F2:$H2)) And then "pulling it down" all the way to the end of your range (i.e., use the mouse to grab the plus sign at the bottom right of cell I9, then click and drag). Would that do the trick, perhaps? Cheers, /MP "enyaw" wrote: How do i ignore the first row? I also need to be able to sum three cells in the row. Any ideas on how to this? This is what i am using. Sub CommandButton4_Click() Dim rng As range, cell As range Set rng = Columns(1).SpecialCells(xlConstants) ' or xlformulas For Each cell In rng cell.Offset(0, 8).Formula = "=sum(9,F2:H2)" Next End Sub "Tom Ogilvy" wrote: Dim rng as Range, cell as Range set rng = Columns(1).Specialcells(xlConstants) ' or xlformulas for each cell in rng cell.offset(0,1).formula = "=if(" & cell.Address(0,0) & _ "<"""",""Filled"",""empty"")" Next -- regards, Tom Ogilvy "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Oh, sorry, I noticed I've been playing with my international settings again :o)
In the formula: =IF($A2="";"";SUM(9;$F2:$H2)) You should of course replace the semicolons (;) with commas (,) =IF($A2="","",SUM(9,$F2:$H2)) Cheers, /MP "Mat P:son" wrote: Quick and dirty (using RC notation for the formula would be neater though): ============================== Private Sub CommandButton4_Click() Dim rng As Range, cell As Range Set rng = Columns(1).SpecialCells(xlConstants) Dim row As Long For Each cell In rng row = cell.row If row 1 Then cell.Offset(0, 8).Formula = _ "=sum(9,F" & row & ":H" & row & ")" End If Next cell End Sub ============================== But I can't help wondering: do you really need a macro to do this? Maybe using a macro is a bit of overkill for this particular problem...? Couldn't you use a normal Excel formula instead, and copy it to the cells you want to fill in? What about putting this into cell I9: =IF($A2="";"";SUM(9;$F2:$H2)) And then "pulling it down" all the way to the end of your range (i.e., use the mouse to grab the plus sign at the bottom right of cell I9, then click and drag). Would that do the trick, perhaps? Cheers, /MP "enyaw" wrote: How do i ignore the first row? I also need to be able to sum three cells in the row. Any ideas on how to this? This is what i am using. Sub CommandButton4_Click() Dim rng As range, cell As range Set rng = Columns(1).SpecialCells(xlConstants) ' or xlformulas For Each cell In rng cell.Offset(0, 8).Formula = "=sum(9,F2:H2)" Next End Sub "Tom Ogilvy" wrote: Dim rng as Range, cell as Range set rng = Columns(1).Specialcells(xlConstants) ' or xlformulas for each cell in rng cell.offset(0,1).formula = "=if(" & cell.Address(0,0) & _ "<"""",""Filled"",""empty"")" Next -- regards, Tom Ogilvy "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
"enyaw" wrote:
I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? It depends -- how will you define the different ranges? If you can do it manually, then the following code will do the trick. (If you need to use some automatic selection, however, you need to specify exactly how you want things done otherwise we can't help you.) To use the following: put the code into e.g. the ThisWorkbook code module. Then select a range, run the macro, and that's it. The way the macro currently works is that it simply outputs some Square Root functions in column C, but you can obviously change that very easily to what ever function you want, and whatever column... Cheers, /MP ============================= Option Explicit ' TODO: ' To which column do you want the formulae ' to be output? I've randomly chosen col C Private Const TargetColumn As Integer = 3 ' You said col A should be used to determine ' whether or not a row should get a formula. Private Const DecisionColumn As Integer = 1 ' This is the main method. Select a range, ' and invoke this method to fill in cells. Private Sub FillInFuncs() If (Selection Is Nothing) Then Exit Sub FillInFuncsHelper _ Selection.Rows(1).row, _ Selection.Rows(Selection.Rows.Count).row End Sub ' A little helper to do the actual job. ' If you want to change the way we get ' the first and last row, you'll change ' FillInFuncs and leave this one as is. Private Sub FillInFuncsHelper(lFirstRow As Long, lLastRow As Long) Dim row As Long For row = lFirstRow To lLastRow If Len(Cells(row, DecisionColumn).Value) 0 Then ' TODO: Change the formula into something more suitable Cells(row, TargetColumn).FormulaR1C1 = "=SQRT(RC1)" End If Next row End Sub ============================= |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
How could i create an undo button that would undo all of this code?
Private Sub CommandButton2_Click() Columns("A:H").Select Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True range("A1").Select Call DeleteBlankRows_2 Dim rng As range, cell As range Set rng = Columns(1).SpecialCells(xlConstants) Dim row As Long For Each cell In rng row = cell.row If row 1 Then cell.Offset(0, 8).Formula = _ "=sum(F" & row & ":H" & row & ")" End If Next cell End Sub "Mat P:son" wrote: "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? It depends -- how will you define the different ranges? If you can do it manually, then the following code will do the trick. (If you need to use some automatic selection, however, you need to specify exactly how you want things done otherwise we can't help you.) To use the following: put the code into e.g. the ThisWorkbook code module. Then select a range, run the macro, and that's it. The way the macro currently works is that it simply outputs some Square Root functions in column C, but you can obviously change that very easily to what ever function you want, and whatever column... Cheers, /MP ============================= Option Explicit ' TODO: ' To which column do you want the formulae ' to be output? I've randomly chosen col C Private Const TargetColumn As Integer = 3 ' You said col A should be used to determine ' whether or not a row should get a formula. Private Const DecisionColumn As Integer = 1 ' This is the main method. Select a range, ' and invoke this method to fill in cells. Private Sub FillInFuncs() If (Selection Is Nothing) Then Exit Sub FillInFuncsHelper _ Selection.Rows(1).row, _ Selection.Rows(Selection.Rows.Count).row End Sub ' A little helper to do the actual job. ' If you want to change the way we get ' the first and last row, you'll change ' FillInFuncs and leave this one as is. Private Sub FillInFuncsHelper(lFirstRow As Long, lLastRow As Long) Dim row As Long For row = lFirstRow To lLastRow If Len(Cells(row, DecisionColumn).Value) 0 Then ' TODO: Change the formula into something more suitable Cells(row, TargetColumn).FormulaR1C1 = "=SQRT(RC1)" End If Next row End Sub ============================= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Well, since you can't call the Application.Undo() method (it only applies to
actions taken by the user, and not to things you do in VBA code), you have to undo these operations manually, I suppose (SAVE YOUR DATA BEFORE YOU TRY THIS OUT!!!) ================== Private Sub CommandButton2_Click() Dim rng As Range, cell As Range Set rng = Columns(1).SpecialCells(xlConstants) ' Still have to avoid the first row, hence the If statement For Each cell In rng If cell.row 1 Then cell.Offset(0, 8).Clear Next cell Columns("A:H").Select Selection.RemoveSubtotal End Sub ================== "enyaw" wrote: How could i create an undo button that would undo all of this code? Private Sub CommandButton2_Click() Columns("A:H").Select Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True range("A1").Select Call DeleteBlankRows_2 Dim rng As range, cell As range Set rng = Columns(1).SpecialCells(xlConstants) Dim row As Long For Each cell In rng row = cell.row If row 1 Then cell.Offset(0, 8).Formula = _ "=sum(F" & row & ":H" & row & ")" End If Next cell End Sub "Mat P:son" wrote: "enyaw" wrote: I want to insert a formula into a row if the first cell in the row has something in it. I need to check through a range of cells but the range of cells will be different every time. How would i program this? It depends -- how will you define the different ranges? If you can do it manually, then the following code will do the trick. (If you need to use some automatic selection, however, you need to specify exactly how you want things done otherwise we can't help you.) To use the following: put the code into e.g. the ThisWorkbook code module. Then select a range, run the macro, and that's it. The way the macro currently works is that it simply outputs some Square Root functions in column C, but you can obviously change that very easily to what ever function you want, and whatever column... Cheers, /MP ============================= Option Explicit ' TODO: ' To which column do you want the formulae ' to be output? I've randomly chosen col C Private Const TargetColumn As Integer = 3 ' You said col A should be used to determine ' whether or not a row should get a formula. Private Const DecisionColumn As Integer = 1 ' This is the main method. Select a range, ' and invoke this method to fill in cells. Private Sub FillInFuncs() If (Selection Is Nothing) Then Exit Sub FillInFuncsHelper _ Selection.Rows(1).row, _ Selection.Rows(Selection.Rows.Count).row End Sub ' A little helper to do the actual job. ' If you want to change the way we get ' the first and last row, you'll change ' FillInFuncs and leave this one as is. Private Sub FillInFuncsHelper(lFirstRow As Long, lLastRow As Long) Dim row As Long For row = lFirstRow To lLastRow If Len(Cells(row, DecisionColumn).Value) 0 Then ' TODO: Change the formula into something more suitable Cells(row, TargetColumn).FormulaR1C1 = "=SQRT(RC1)" End If Next row End Sub ============================= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Formula | Excel Discussion (Misc queries) | |||
inserting formula | Excel Programming | |||
inserting a formula | Excel Discussion (Misc queries) | |||
Formula changes while inserting a row !!!! | Excel Worksheet Functions | |||
Inserting Formula | Excel Programming |