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

Topic

New Reply

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: =INDEX(‘Staffing Numbers Database’!A1:A4,’Staffing Numbers Database’!E1) 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 Sub of Function not defined 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

Reply | Quote

Replies

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

Steve

Reply | Quote

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.

Reply | Quote