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?

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?

"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

=============================

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?

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?

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?

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

=============================

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

=============================