Case Reference??

[Gregorys05]

Hi guy's i have the below code, it works fine but i have hit a slight snag.

Where i am referencing the day overview tab and pulling the value of R47C12 back, i have a problem that the cell i want to referance my change due to peope adding more row above.

The row i am after is called total and the value is in L.
How can i get the code to always look at the value in L no matter if its row 47 or 147?

Columns(A,F,G,H,I,J,K,L)
<TABLE style="WIDTH: 441pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=587 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230; mso-outline-parent: collapsed" width=61><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" width=155 height=21>VOTINGRHTS</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=61> </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffff99; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 51pt; COLOR: yellow; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Trebuchet MS'; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=68> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 31pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=41> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 32pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=43> </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=68> </TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=65> </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffff99; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; COLOR: green; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Trebuchet MS'; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=86> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: silver" height=24>Total</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver">672</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver">42:36:32</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver">03:48</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver"></TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver">44:48:00</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver">-5%</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: green; BORDER-BOTTOM: windowtext 1pt solid; FONT-FAMILY: 'Trebuchet MS'; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">105%</TD></TR></TBODY></TABLE>

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
Dim i As Integer
If Target.Cells.Count > 1 Then Exit Sub
i = Target.Column
If Intersect(ActiveCell, Range("A1:A36")) Is Nothing Then
        Exit Sub
    
    Else

Select Case i
Case 1:     ActiveCell.Offset(0, 3).FormulaR1C1 = "='DAY OVERVIEW'!R47C12"
            ActiveCell.Offset(0, 5).FormulaR1C1 = "='DAY OVERVIEW'!R47C23"
            ActiveCell.Offset(0, 7).FormulaR1C1 = "='DAY OVERVIEW'!R47C34"
            ActiveCell.Offset(0, 13).FormulaR1C1 = "='DAY OVERVIEW'!R47C78"
            ActiveCell.Offset(0, 14).FormulaR1C1 = "='MONTH OVERVIEW'!R47C78"
            
            ActiveCell.Offset(0, 3).Copy
            ActiveCell.Offset(0, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            ActiveCell.Offset(0, 2).Copy
            ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            ActiveCell.Offset(0, 2).Copy
            ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            ActiveCell.Offset(0, 6).Copy
            ActiveCell.Offset(0, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            ActiveCell.Offset(0, 1).Copy
            ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveSheet.Paste
            Application.CutCopyMode = False
                  
End Select
End If
Application.ScreenUpdating = True
End Sub

Thanks

 

[pgc01]

Hi Gregory

Define a named range referring to the cell. If the cell has a name you have no problems with insertion/deletion of rows/columns, the name always refers to the cell.