Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDIRECT Question..
hi, I have some script recently had help with.
for ranges / cells described, would like to use an INDIRECT reference in script, to cells that will not be moved (to reference cells that might be moved) the cells I am referencing using formula's that follow. correlation is listed below, but imagine that I can insert where needed if I can get the correct function.. thanks cell B1 contains: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DN$6),"$"," "),"","") cell B2 / C2 contains: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"") =SUBSTITUTE(SUBSTITUTE(CELL("address",$DT2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DT2),"$",""),ROW(),"") which all output, in sheet, as follows (related to script using below) DN6 in cell B1 DU:DU DT:DT B2 C2 EE:EY EF1 B3 C3 FE:FV FG:FX B4 C4 EC:ED FE:FF B5 C5 CK:CO CF B6 C6 CW:CW CG B7 C7 SCRIPT: Option Explicit Private Sub CommandButton1_Click() If Range("DN6").Value = "Z" Then '1 col: copy Paste-Values to left 1 col Columns("DU:DU").Select Selection.Copy Range("DT:DT").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False '20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols Columns("FE:FV").Select Selection.Copy Range("FG:FX").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double col: (1 set of 2), COPY: Paste-Values to different section Columns("EC:ED").Select Selection.Copy Range("FE:FF").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Yet Another 'INDIRECT' Question | Excel Worksheet Functions | |||
INDIRECT Question | Excel Discussion (Misc queries) | |||
INDIRECT Question I think | Excel Discussion (Misc queries) | |||
Indirect question | Excel Discussion (Misc queries) | |||
indirect.ext question | Excel Discussion (Misc queries) |