formula does not work in macro
rng.Offset(0, 1).Formula = _
"=SUMIF(c1:c101,""esn-personal"",e1:e101)"
make a similar correction to all you formulas
quotes used within quotes need to be doubled.
--
Regards,
Tom Ogilvy
mary wrote in message
...
Can some please please edit this macro for me? Possibly
TOM because
he has been extremely helpful. He gave me most of this
formula and i edited it to
add more formula and now it does not work. I have
recorded the
added formula but the recording formula refers to a
specific cell
; where as, i will like for it to find a designated cell
and
sum the totals. example: find vacation1 and sum all
vacation after
Comparing two other cells. I will be grateful with any
assistant.
this is the macro: I have also added notes in the macro
Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("esn-personal1")
If Not rng Is Nothing Then
'the formula below does not work in this macro but works
well on the excel sheet
'it should find esn-personal1 and add this formula
rng.Offset(0, 1).Formula = "=SUMIF(c1:c101,"esn-
personal",e1:e101)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
Set rng = Cells.Find("E-Time1")
If Not rng Is Nothing Then
'the formula below does not work in this macro but works
well on the excel sheet
'it should find esn-personal1 and add this formula
rng.Offset(0, 1).Formula = "==SUMIF
(c1:c101,"vacation",e1:e101)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
Set rng = Cells.Find("sick1")
If Not rng Is Nothing Then
'the formula below does not work in this macro but works
well on the excel sheet
'it should find esn-personal1 and add this formula
rng.Offset(0, 1).Formula = "=SUMIF
(c1:c101,"vacation",e1:e101)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
'the formula below does not work in this macro but works
well on the excel sheet
'it should find esn-personal1 and add this formula
rng.Offset(0, 1).Formula = "=SUMIF
(c1:c101,"vacation",e1:e101)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
Set rng = Cells.Find("Lunch1")
If Not rng Is Nothing Then
'the formula below does not work in this macro but works
well on the excel sheet
'it should find esn-personal1 and add this formula
rng.Offset(0, 1).Formula = "=SUMIF
(c1:c101,"vacation",e1:e101)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
'the formula below should find total1 subtract "lunch"
from "total")
Set rng = Cells.Find("total1")
If Not rng Is Nothing Then
rng.Offset(0, 1).FormulaR1C1 = "=R[-2]C-R[-1]C"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
End Sub
|