Data validation formulas -convert from absolute to relative cell ref.

[didijaba]

Hi, I have plenty of cells with data validations formulas, all in absolute format ($A$1). Do you know how to convert them to A1 format? I have tried with add validation, but didn't work.

Sub RELATIVE_REFERENCE()
Dim x As Range
On Error Resume Next
Selection.Offset(0, 5).Value = Right(Selection.Validation.Formula1, Len(Selection.Validation.Formula1) - 1)
Selection.Offset(0, 5).Value = Application.WorksheetFunction.Substitute(Selection.Offset(0, 5).Text, "$", "")
x = Selection.Offset(0, 5)
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & x & ")"


.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


'.Formula = Application.ConvertFormula(Formula:=ActiveCell.Offset(0, 5).Value, fromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

End Sub

 

[L. Howard]

I found this, which seems to do that.

Select the cell/s before running or on first prompt.

I altered xName to xName = "$$ or Not $$"

Howard

Option Explicit

Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As String
Dim xIndex As Integer
On Error Resume Next
'xTitleId = "KutoolsforExcel"
xName = "$$ or Not $$"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xName, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xName, 1, Type:=1)
For Each Rng In WorkRng
    Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub

 

[didijaba]

Thanks Howard,
I have tried it but my goal is to change cells data validation formula, not cells formula. I have tried to apply this to validation.formula1 but it shows error.
Here is what I tried.

Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As String
Dim xIndex As Integer
On Error Resume Next
'xTitleId = "KutoolsforExcel"
xName = "$$ or Not $$"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xName, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xName, 1, Type:=1)
For Each Rng.Validation.Formula1 In WorkRng
    Rng.Validation.Formula1 = Application.ConvertFormula(Rng.Validation.Formula1, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[L. Howard]

Okay, I see. I would not know how to do that to already existing DV drop downs using code.

What is the reason to go from $A$1 to A1 in all the DV drop downs?

Howard

 

[didijaba]

I have compatibility problems with file working in various versions of excel (nested formulas, links to other sheets...) . So I have to take complex formulas out of cell validation, and return result to validation using INDIRECT. Problem is I have not formated cell to relative before input, so I have to do this now I have to copy cells and validation has to adjust to row change).

 

[L. Howard]

I wonder if you could use something like one of these formula.


Where:

The first will include entries from A2 to A200
The second will include entries from B10 to B20
The third will include all entries in column C

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

=OFFSET($B$10,0,0,COUNTA($B$10:$B$20),1)

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

Howard

 

[didijaba]

Thanks Howard, I used this on other thing, but if helped me a lot .