Exchange xlR1C1 and xlA1 Formats
Sometimes, Excel will automatically change formulas from xlR1C1 format to xlA1 format... but not always. When it doesn't convert it automatically, usually a run time error 1004 is invoked. The following example shows two approaches to this issue. The first example actually converts the formula from one format to the other. The second example changes the default format used by Excel.
Program Code
Option Explicit
Public Sub LoadDataValidationList()
Dim wksMainSheet As Worksheet
Dim strValidationFormula As String
Set wksMainSheet = ThisWorkbook.Sheets("Sheet1")
strValidationFormula = "=Sheet2!$D$1:$D$7"
' *************************************************************************
' Convert Formulas from xlA1 to xlR1C1 Format
' *************************************************************************
If Application.ReferenceStyle = xlR1C1 Then
strValidationFormula = Application.ConvertFormula(strValidationFormula, xlA1, xlR1C1)
End If
Call LoadDataValidation(wksMainSheet, 10, 1, strValidationFormula, True)
End Sub
Public Sub LoadDataValidation(TargetSheet As Worksheet, TargetRow, TargetColumn, DropDownFormula, ShowUnmatchedErrors)
With TargetSheet.Cells(TargetRow, TargetColumn).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=DropDownFormula
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = ShowUnmatchedErrors
End With
End Sub
' *************************************************************************
' Change The Excel Formula Style
' *************************************************************************
Option Explicit
Public Sub ConvertFormulaStyles()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
End If
End Sub