Thread
:
Place formula in cells
View Single Post
#
8
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Place formula in cells
One way combining posts
Sub makeforulasifFormula() 'Me
sr = 2
With Range("a1:a500")
Set c = .Find("~*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mr = c.Row
'one formula
c.Offset(, 1).Formula = "=sum(b" & sr & ":b" & mr - 1 & ")"
Range("B" & mr).Copy Range("B" & mr & ":H" & mr)
sr = mr + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"pmxgs" wrote in message
...
Hi,
thanks very much for your help.
It's doing what I want except for one thing.
This procedure is summing the correct rows but leaves the resulting
calculation as values, and I need to have the actual sum function in those
cells (the built in sum worksheet function), as if I typed directly in
the
cells "=Sum(b3:b15)"
"Don Guillett" wrote:
try
Sub SumRangesIf()
sr = 2
With Range("a1:a500")
Set c = .Find("~*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mr = c.Row
For i = 1 To 7
c.Offset(, i) = Application.Sum(Range(Cells(sr, i + 1), Cells(mr -
1, i
+ 1)))
Next i
sr = mr + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"pmxgs" wrote in message
...
Hi,
Can someone please help me how to write this procedure.
I'm just beginning to learn vba so I don't know which methods and
properties to use.
What I want to do is this:
Go through every row (3 until 150) on column A and if the character "*"
(asterisk) is found in each of these rows then write the following
formulas
on each column. ex: sum(b3:b15),sum(c3:c15)....sum(h3:h15) assuming
that
the
asterisk is found on row 15.
Then start looking for an asterisk again and when it is found write the
same
formulas , but this time the sum will only include the rows that were
not
included previoulsy.ex: sum(b16:b28),sum(c16:c28)....sum(h16:h28) (I'm
assuming the asterisk is found on row 28).
The sum has to be written in every row which has an asterisk, the
formula
is
repeated in every column from B to H.
thanks
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett