relative address in validate

[miro]

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.

 

[Bob Phillips]

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]

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.

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)

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.

 

[Bob Phillips]

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)

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" <[email protected]> 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)

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.

 

[miro]

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.

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)

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" <[email protected]> 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)


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.