HI.
with this code I put the formula in B1 with absolute reference to cell A1:

Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=A1"
...
...
End With

..

Is possible by vba insert a relative address?
If I use

formular1c1:="RC[-1]"

the code doesn't word.

Any help will be appreciated.
Best rgds
M.

How about this?


Range("K1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ColumnLetter(ActiveCell.Column - 1) &
ActiveCell.Row
....
....
End With

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
HI.
with this code I put the formula in B1 with absolute reference to cell A1:

Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=A1"
...
...
End With

.

Is possible by vba insert a relative address?
If I use

formular1c1:="RC[-1]"

the code doesn't word.

Any help will be appreciated.
Best rgds
M.

MMM.. thanks Bob, but the formula in validate is more complicated.

Formula1:= "=OFFSET($AA$11,MATCH( D10
,$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"

I need that D10 is not absolute but relative (2 columns left from cell where
the formula is put)

Thanks
M.


"Bob Phillips" ha scritto nel messaggio
...
How about this?


Range("K1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ColumnLetter(ActiveCell.Column - 1) &
ActiveCell.Row
...
...
End With

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
HI.
with this code I put the formula in B1 with absolute reference to cell
A1:

Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=A1"
...
...
End With

.

Is possible by vba insert a relative address?
If I use

formular1c1:="RC[-1]"

the code doesn't word.

Any help will be appreciated.
Best rgds
M.

Same principle applies,

"=OFFSET($AA$11,MATCH( & ColumnLetter(ActiveCell.Column - 2) &
ActiveCell.Row
& ",$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
MMM.. thanks Bob, but the formula in validate is more complicated.

Formula1:= "=OFFSET($AA$11,MATCH( D10
,$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"

I need that D10 is not absolute but relative (2 columns left from cell
where
the formula is put)

Thanks
M.


"Bob Phillips" ha scritto nel
messaggio
...
How about this?


Range("K1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ColumnLetter(ActiveCell.Column - 1) &
ActiveCell.Row
...
...
End With

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
HI.
with this code I put the formula in B1 with absolute reference to cell
A1:

Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=A1"
...
...
End With

.

Is possible by vba insert a relative address?
If I use

formular1c1:="RC[-1]"

the code doesn't word.

Any help will be appreciated.
Best rgds
M.

Thank you Bob!
In the night (I'm write from Italy) I've found another simply way:

Formula1:= "=OFFSET($AA$11,MATCH(" & ActiveCell.Offset(, -2).Address &
",$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"

Bye
M.


"Bob Phillips" ha scritto nel messaggio
...
Same principle applies,

"=OFFSET($AA$11,MATCH( & ColumnLetter(ActiveCell.Column - 2) &
ActiveCell.Row
& ",$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
MMM.. thanks Bob, but the formula in validate is more complicated.

Formula1:= "=OFFSET($AA$11,MATCH( D10
,$AA$11:$AA$106),0):OFFSET($AA$11,95,0)"

I need that D10 is not absolute but relative (2 columns left from cell
where
the formula is put)

Thanks
M.


"Bob Phillips" ha scritto nel
messaggio
...
How about this?


Range("K1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ColumnLetter(ActiveCell.Column - 1) &
ActiveCell.Row
...
...
End With

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"miro" wrote in message
...
HI.
with this code I put the formula in B1 with absolute reference to cell
A1:

Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=A1"
...
...
End With

.

Is possible by vba insert a relative address?
If I use

formular1c1:="RC[-1]"

the code doesn't word.

Any help will be appreciated.
Best rgds
M.