Hi Nastech,

What about using Find to locate something that never changes in the
column/s. For example the column header. The following examples demonstrate a
couple of methods. See the comments in the code for more information as to
what they do.

Sub Macro1()

'The following finds the column header and
'sets a variable that can be used in lieu
'of the cell range.

Dim objCol As Object
Dim strToFind As String

strToFind = "My Col Header 1"

Set objCol = Cells.Find _
(What:=strToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'Test that string found and re-set
'variable to entire column
If Not objCol Is Nothing Then
Set objCol = objCol.EntireColumn

'Following line is example of using the variable
'in lieu of the range.
objCol.Select

Else
MsgBox strToFind & " not found"
End If


'The following names the range after the above Find.
'The named range can then be used in lieu
'of the actual range.
'Note: Named ranges are saved with the WorkBook.
'It is the same as naming a range in the
'interactive mode on the worksheet.

If Not objCol Is Nothing Then
ActiveWorkbook.Names.Add Name:="MyFirstCol", _
RefersToR1C1:=objCol.EntireColumn

'Following is example of using the named
'range in lieu of the actual range
Range("MyFirstCol").Select

Else
MsgBox strToFind & " not found"
End If



End Sub


--
Regards,

OssieMac


"Nastech" wrote:

hi, thanks for responses. sorry if questions sometimes vague. am not sure
what is pertinent vs. posting too much. (work on advanced items?.. slower
with some area's novice: spend much time to develope..).

:) not even sure if your answers apply.. new to scripting.. macro's, you
may need to tell me if your anwers apply to what doing, as not sure. thanks.


Will include script below that references, mostly columns. work-around is
for some kind of INDIRECT() reference to desired cells, from cells that will
not be moved, i.e. programming will never have to change.. ?? (not sure on
that), by use of either just typing the COLUMN LETTER reference in cells
listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC"
response:

=SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"")

gets: DU:DU
so if need any else such as quotes.. around "DU:DU", I can add to that
formula as well.

IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me
know, will work on but take some time for me to try.

PROBLEM: when add/ remove columns, have to manually change script.
SUBSTITUTE formula is work around.

Script using is:


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








"OssieMac" wrote:

Hi,

I have read your question many times and still I am not sure if I am
correctly interpreting what it is that you want. I am assuming that you want
to reference Cells and Ranges using variables instead of hard coding the
actual Cell/Range address. Have a look at the following examples of
referencing cells and ranges in VBA code and see if it answers your question.

Note: When using Cells function to reference cells, the row is first
followed by the column. That is Cells(RowNumber,ColNumber). This is back to
front to the way they are referenced with Range(ColId : RowNumb)

Dim lngRowNumber As Long
Dim lngColNumber As Long

Dim strCol1 As String
Dim strCol2 As String

lngRowNumber = 4
lngColNumber = 5

'Following same as Range("E5").Select
Cells(lngRowNumber, lngColNumber).Select

'Following same as Range("E4:G6").Select
Range(Cells(lngRowNumber, lngColNumber), Cells(6, 7)).Select

strCol1 = "F"
'Following same as Range("F:G").Select
Range(strCol1 & ":G").Select

strCol2 = "H"
'Following same as Range("F:H").select
Range(strCol1 & ":" & strCol2).Select


--
Regards,

OssieMac


"Nastech" wrote:

hi, is there a way to modify the reference to columns,
to be from a different single cell, such as INDIRECT..
within a macro / script? Thanks

the type of lines I want to reference a

Range("A1").Value
Columns("B:C").Select
Range("D:E").Select

Intersect(Me.Range("F:G"),
With Me.Cells(.Row, "H")