vb, combobox, RefersToRange, Dynamic Named Ranges

Question

Can anyone Please help resolve an issue with ReferToRange in my code. I have attached an example. I am getting a runtime error 1041 application defined or object defined error when the MAIN is called. I am linking a combobox listfillrange to 3 named ranges depending on the value of a cell. The three ranges are dynamic(have an offset formula). the combobox is a different sheet than the named ranges Please help

Sub MAIN()
Dim PT As Range
Dim i As Long

With Sheet3  ' Unique SPP
    setNames .Range("a6")
    Set PT = .Range("b1")
    i = 1
    Do Until PT = ""
        If .Range("a1").Value = PT.Value Then
            On Error Resume Next
            Sheet1.ComboBox1.ListFillRange = ThisWorkbook.Names("view" & i).Name
            If Err.Number = 1004 Then
                MsgBox "not defined name: view" & i
            ElseIf Err.Number <> 0 Then
                MsgBox "unexpected error: " & Err.Description
            End If
            On Error GoTo 0
        End If
        i = i + 1
        Set PT = PT.Offset(0, 1)
    Loop
End With
End Sub

Sub setNames(theTopLeft As Range)
    Dim theName As Name
    Dim nameStr As String
    Dim theRng As Range
    Dim i As Long
    Application.DisplayAlerts = False
    theTopLeft.CurrentRegion.CreateNames Top:=True, Left:=False, _
                Bottom:=False, Right:=False
    Application.DisplayAlerts = True
    For Each theName In ThisWorkbook.Names
        With theName.RefersToRange.Value
            For i = .Cells.Count To 1 Step -1
                If .Cells(i) <> "" Then Exit For
            Next
        End With
        If i <> 0 Then theName.RefersTo = theName.RefersToRange.Resize(i, 1)
    Next
End Sub

This looks like VBA and not VB.NET. Also, it seems that this is in Excel, so the excel-vba tag should be added. The dynamic tag probably has no place here. — Zev Spitz, Nov 13 '13 at 18:15
I am getting a runtime error 1041 application defined or object defined error when the MAIN is called. The referToRange is highlighted, Sorry if i was not clear. Thank you for looking at my post — user25830, Nov 13 '13 at 19:47

DaveU · Accepted Answer · 2013-11-14T05:17:55.107

0

It seems to me that your code is a bit more complicated than necessary. So if I'm understanding correctly what you're trying to do, this should fit the bill.

Sub MAIN()

Dim rC As Range
Dim rD As Range
Dim i As Long
Dim s As String

On Error GoTo errTrap

With Sheet3 'change to suit
    s = .Range("a1") 'heading to find
    Set rD = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell)) 'data row 6 and down
    Set rD = rD.Resize(, 3) '1st 3 columns only, change if required
    i = Application.Match(s, rD.Rows(1).Cells, 0) 'find heading
    Set rC = rD.Columns(i).Offset(1).Cells 'drop heading from column
    Set rC = .Range(rC(1), .Cells(.Rows.Count, rC.Column).End(xlUp)) 'to end of data
'       if column contains data, fill combo
    If rC(1).Row > rD.Row Then Sheet1.ComboBox1.ListFillRange = .Name & "!" & rC.Address
End With
Exit Sub
errTrap:
If Err.Number = 13 Then
    MsgBox "heading not found:  " & s
Else
    MsgBox "unexpected error: " & Err.Description
End If

End Sub

enter image description here

edited Nov 14 '13 at 05:17

answered Nov 14 '13 at 01:27

DaveU

1,082
2
14
25

Thanks! Your code is much more elegant. I could not get it to work, i am getting column heading not found. I tried moving moving my data to fit the current range, but the combobox fillrange didn't change. My column Headings are in A6, B6 and C6 of sheet3. Can you help me troubleshoot this? Thanks again for your help – user25830 Nov 14 '13 at 02:32
Oh, I assumed your headings were in row 1 - let me look at this again, and I'll get back to you. So just to be sure I understand, you have 3 columns of data (A,B & C), and headings are in row 6 - correct? – DaveU Nov 14 '13 at 03:42
Correct, i am grateful for you help – user25830 Nov 14 '13 at 03:50
Thanks Dave, it is picking up the headings but the combobox is only populated when the heading from the first column is picked. the second and third column are not populating the combo. I am working on making a loop. Thanks a lot! – user25830 Nov 14 '13 at 04:37
It's working for me - maybe you could post a sample of your data. – DaveU Nov 14 '13 at 04:59
I just added a sample of my data, does yours look like that? Btw, you shouldn't require a "loop", as far as I can see. – DaveU Nov 14 '13 at 05:18
Awesome! Thanks late night was tired. Will make a donation to St Jude in your name. Much gratitude! – user25830 Nov 14 '13 at 05:53

vb, combobox, RefersToRange, Dynamic Named Ranges

1 Answers1