Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macro - propogate formula
I am trying to use an event macro referenced in another thread,
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick Target.Offset(1).EntireRow.Insert Target.EntireRow.Copy target.Offset(1).EntireRow Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents End Sub (credit to: http://www.mvps.org/dmcritchie/excel/insrtrow.htm) but am getting a debug message on the following line: Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents I need clarification. Any help is appreciated. Thanks, Janice |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macro - propogate formula
If there are no constants, SpecialCells will throw an error. One
modification: Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick With Target.EntireRow .Offset(1, 0).Insert .Copy .Offset(1, 0).Cells On Error Resume Next .Offset(1, 0).SpecialCells(xlConstants).ClearContents On Error GoTo 0 End With End Sub In article , jct wrote: but am getting a debug message on the following line: Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents I need clarification. Any help is appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macro - propogate formula
Hi John,
Thanks for the correction, I've updated several event macros on my insrtrow.htm page. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "JE McGimpsey" wrote... If there are no constants, SpecialCells will throw an error. One modification: |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event Macro - propogate formula
Thanks, that worked. I'm feeling around with macros and learning as I go.
Maybe you know if there's a better way to do what I'm trying to accomplish... I want to insert column A:C of a pivot table on Sheet1 into Sheet2, which contains additional formulas. I tried just inserting the same pivot table in Sheet2, but the formulas do not automatically fill in on the adjacent cells (D:M). Any ideas? What I'm doing now is updating the pivot table in Sheet1 and using the double-click event macro to fill in any additional rows. Clumsy, but it works. Thanks, Janice "JE McGimpsey" wrote: If there are no constants, SpecialCells will throw an error. One modification: Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick With Target.EntireRow .Offset(1, 0).Insert .Copy .Offset(1, 0).Cells On Error Resume Next .Offset(1, 0).SpecialCells(xlConstants).ClearContents On Error GoTo 0 End With End Sub In article , jct wrote: but am getting a debug message on the following line: Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents I need clarification. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving the formula "average" over one column in a macro | Excel Worksheet Functions | |||
Help on Macro or Formula | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Fill Formula - Macro | Excel Discussion (Misc queries) | |||
Can you enter a formula in a cell to run a macro? | Excel Worksheet Functions |