Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula does not work in macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
formula to work in a 3-d reference with 2 work books | Excel Worksheet Functions | |||
A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) | |||
Can a macro be made to work based on a formula? | Excel Discussion (Misc queries) | |||
Can a macro be made to work based on a formula? | Excel Discussion (Misc queries) |