On Thursday, August 15, 2013 11:50:32 AM UTC-5, Claus Busch wrote:
Hi, Am Thu, 15 Aug 2013 08:16:52 -0700 (PDT) schrieb : and received the error of "Exception from HRESULT: 0x800A03EC". It means that Formula was not found. try: Sub ValidationItems() Dim strSh As String Dim strRng As String Dim myStr As String Dim myArr As Variant Dim i As Integer Dim rngC As Range With Sheets("Sheet1").Range("A1").Validation If InStr(.Formula1, "=") = 0 Then myStr = .Formula1 myArr = Split(myStr, ";") End If If InStr(.Formula1, "!") 0 Then strSh = Mid(.Formula1, 2, InStr(.Formula1, "!") - 1) strRng = Mid(.Formula1, InStr(.Formula1, "!") + 1, 99) ElseIf InStr(.Formula1, "=") 0 Then strRng = Replace(.Formula1, "=", "") End If If strSh < "" Then ReDim myArr(Sheets(strSh).Range(strRng).Cells.Count - 1) For Each rngC In Sheets(strSh).Range(strRng) myArr(i) = rngC i = i + 1 Next ElseIf strSh = "" And strRng < "" Then ReDim myArr(Range(strRng).Cells.Count - 1) For Each rngC In Range(strRng) myArr(i) = rngC i = i + 1 Next End If End With Range("L1").Resize(UBound(myArr) + 1) = _ WorksheetFunction.Transpose(myArr) End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Hi,

I tried your code (I use VB2010) on Isabelle's excel file, it worked fine. But not on my excel file. Again the error message pops up: Exception from HRESULT: 0x800A03EC.

Thanks very much for your help.