Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using INDEX in VB (Office 2000 SR-1, Excel2000)

    I may be close to a solution but braingfog is setting in. This is probably a matter of syntax...

    I have a combo box that grabs a range programmatically when a new Case Number is added to the worksheet. I need to reference the actual case number selected in the listbox and can do this fine using INDEX in the cell on a sheet like so: <font color=448800>=INDEX('Staffing Numbers Database'!A1:A4,'Staffing Numbers Database'!E1)</font color=448800> in the formula bar. However, I need to do this in VB and am not having much luck invoking INDEX in vb.

    Here's the code for grabbing the range for the combo box:

    <--
    Sheets("Staffing Numbers Database").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    a = ActiveWindow.RangeSelection.Address
    MsgBox "Name Range is " & a
    ActiveWorkbook.Names.Add Name:="AvailableCaseNumbers", RefersToR1C1:=a
    b = "'" & "Staffing Numbers Database" & "'" & "!" & a
    Sheets("Select a Follow Up").Select
    Sheets("Select a Follow Up").Shapes("Drop Down 1").Select
    With Selection
    .ListFillRange = b
    .DropDownLines = 8
    .Display3DShading = False
    End With
    </--

    As you can see, I've stuck in a line to define a Name for the range used for the combo box, hopefully to use in declaring the INDEX for a specific cell. but when I try this:

    <--
    Range("c13").Select
    Selection.Value = Index(AvailableCaseNumbers, "'Staffing Numbers Database'!E1")
    </--

    I get a <font color=red>Sub of Function not defined</font color=red> compile error. Also, the Name fails to show in the little Named Range drop-down box.

    Any ideas/suggestions? I need to programmtically define the range used by INDEX to grab the 'contents' of the combo-box, which should be easy enough but the basic compile error has me flummoxed.

    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using INDEX in VB (Office 2000 SR-1, Excel2000)

    IndexValue = sheets("Staffing Numbers Database").range("A1:A4").cells(sheets("Staffing Numbers Database").range("E1").value)

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using INDEX in VB (Office 2000 SR-1, Excel2000)

    Figured out a way to do this. It's a little strange but I just defined a variable and loaded it with the string information you'd normally type in the formula bar:

    <--
    Range("c13").Select
    IndexRange = "=INDEX('Staffing Numbers Database'!" & a & ",'Staffing Numbers Database'!E1)"
    Selection.Value = IndexRange
    </--

    Doesn't invoke INDEX in VB at all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •