unknown range (Excel 2000)

Topic

New Reply

Hi all,

I have a range of values in a worksheet that the user inputs a value. Here’s my question, I don’t know where on the sheet this range is, it changes every day. What I would like to able to do is have the user input the data, and another user change the background colour to indicate it’s the status of the data, in the unknown range.

What I would like to accomplish is this, if the first name in the array is always the same(title), how would I first find that array of information? THEN display the information like I have here in this sub? Is there a built in function that I could use for this to find it?

Dim arrNames()
Range(Cells(4, 3), Cells(4, 3)).Select

Do Until Range(Cells(4 + I, 3), Cells(4 + I, 3)).Value = “”
ReDim Preserve arrNames(I)
arrNames(I) = Range(Cells(4 + I, 3), Cells(4 + I, 3)).Value
I = I + 1
Range(Cells(4 + I, 3), Cells(4 + I, 3)).Select
Loop

UserForm1.ListBox1.Clear
For I = 0 To UBound(arrNames)
UserForm1.ListBox1.AddItem arrNames(I)
Next
UserForm1.Show

End Sub
Thanks, Darryl.

Reply | Quote

Replies

Something like this?

Dim arrNames()
Dim i As Long
Dim oCell As Range

‘ don’t forget to substitute the text you are searching for.
Set oCell = ActiveSheet.Cells.Find(What:=”Title”, LookIn:=xlValues, LookAt:=xlWhole)
If oCell Is Nothing Then
MsgBox “Value not found”, vbExclamation
Exit Sub
End If

i = 0
Do Until oCell.Offset(i, 0).Value = “”
ReDim Preserve arrNames(i)
arrNames(i) = oCell.Offset(i, 0).Value
i = i + 1
Loop

Reply | Quote

Thank you Hans:)

Reply | Quote

Thank you Hans:)

Reply | Quote

Hans Thank you —- How would I show three different strings in three different listboxs?

Reply | Quote

Can you elaborate on what you want to do?

Reply | Quote

OCell as range…If I were to have three seperate lists, displayed in three seperate listboxes where would I what doI need to do with this code so that I could accomplish that?

Do I need to Redim seperate ranges,

Dim arrNames()
Dim i As Long
Dim oCell As Range

Set oCell = ActiveSheet.Cells.Find(What:=”String”, LookIn:=xlValues, LookAt:=xlWhole)
If oCell Is Nothing Then
MsgBox “Value not found”, vbExclamation
Exit Sub
End If
i = 0
Do Until oCell.Offset(i, 0).Value = “”
ReDim Preserve arrNames(i)
arrNames(i) = oCell.Offset(i, 0).Value
i = i + 1
Loop
UserForm1.ListBox1.Clear
UserForm1.ListBox2.Clear —–IS this right?
UserForm1.ListBox3.Clear

For i = 0 To UBound(arrNames)
UserForm1.ListBox1.AddItem arrNames(i)
UserForm1.ListBox1.AddItem arrNames(i) ————–I want to display three different ranges, can it be done?
UserForm1.ListBox1.AddItem arrNames(i)
Next
UserForm1.Show
End Sub

Can it be done?

Thanks, Darryl.

Reply | Quote

The current code finds a specific word (“String”), then puts the contents of that cell and the cells below it into an array, and uses that array to populate a list box. Now, you want to populate 3 list boxes. Where do you want to populate them from? Should they all have the same contents?

By the way, instead of

For i = 0 To UBound(arrNames)
UserForm1.ListBox1.AddItem arrNames(i)
Next

you can use the single instruction

UserForm1.ListBox1.List = arrNames

Reply | Quote

I want to populate them from 3 seperate finds of text- listbox1.additem string1
listbox2.additem string2
listbox3.additem string3

does that help?

Reply | Quote

If I understand you correctly (I’m not sure), you can encapsulate the code to populate a list box in a separate routine, and call that 3 times. First, create a procedure to fill a list box:

Private Sub FillListBox(lb As MSForms.ListBox, st As String)
Dim i As Long
Dim oCell As Range

Set oCell = ActiveSheet.Cells.Find(What:=st, LookIn:=xlValues, LookAt:=xlWhole)
If oCell Is Nothing Then
MsgBox “Value not found”, vbExclamation
Exit Sub
End If

i = 0
Do Until oCell.Offset(i, 0).Value = “”
lb.AddItem oCell.Offset(i, 0).Value
i = i + 1
Loop
End Sub

This procedure has two arguments: the first is the list box to be populated, the second is the string to be searched. In the code that opens the userform, call the procedure once for each list box:

Private Sub StartForm()
FillListBox UserForm1.ListBox1, “String1”
FillListBox UserForm1.ListBox2, “String2”
FillListBox UserForm1.ListBox3, “String3”
UserForm1.Show
End Sub

Reply | Quote

Thank you Hans

Reply | Quote

As I have read this thread I am looking to see what you wish to accomplish. It occurs to me that you are mainly trying to keep
your list boxes CURRENT – up to date as users add or delete items in a range.
If that’s the case, here’s another approach:

1. Create a Named Range (String1) but make it DYNAMIC: (Assume String1 is in A1)
(Insert>Name > RefersTo: =Offset(Sheet1!$A$1,1,0,CountA(Sheet1!$A$:A500),1)
(The A500 needs to be larger than any anticipated need)
2. When you set up the userform listbox, manually set the ListFillRange one time only to String1

Nothing further is needed at run time as long as there are no blanks in the list.
However, if I understand your loop that creates the arrNames, it too will stop
adding items when it encounters a blank in the list.

If the above interests you, I have developed a way to get around the “blank” issue
that I can share with you
HTH

Reply | Quote

As I have read this thread I am looking to see what you wish to accomplish. It occurs to me that you are mainly trying to keep
your list boxes CURRENT – up to date as users add or delete items in a range.
If that’s the case, here’s another approach:

1. Create a Named Range (String1) but make it DYNAMIC: (Assume String1 is in A1)
(Insert>Name > RefersTo: =Offset(Sheet1!$A$1,1,0,CountA(Sheet1!$A$:A500),1)
(The A500 needs to be larger than any anticipated need)
2. When you set up the userform listbox, manually set the ListFillRange one time only to String1

Nothing further is needed at run time as long as there are no blanks in the list.
However, if I understand your loop that creates the arrNames, it too will stop
adding items when it encounters a blank in the list.

If the above interests you, I have developed a way to get around the “blank” issue
that I can share with you
HTH

Reply | Quote

Thank you Hans

Reply | Quote

If I understand you correctly (I’m not sure), you can encapsulate the code to populate a list box in a separate routine, and call that 3 times. First, create a procedure to fill a list box:

Private Sub FillListBox(lb As MSForms.ListBox, st As String)
Dim i As Long
Dim oCell As Range

Set oCell = ActiveSheet.Cells.Find(What:=st, LookIn:=xlValues, LookAt:=xlWhole)
If oCell Is Nothing Then
MsgBox “Value not found”, vbExclamation
Exit Sub
End If

i = 0
Do Until oCell.Offset(i, 0).Value = “”
lb.AddItem oCell.Offset(i, 0).Value
i = i + 1
Loop
End Sub

This procedure has two arguments: the first is the list box to be populated, the second is the string to be searched. In the code that opens the userform, call the procedure once for each list box:

Private Sub StartForm()
FillListBox UserForm1.ListBox1, “String1”
FillListBox UserForm1.ListBox2, “String2”
FillListBox UserForm1.ListBox3, “String3”
UserForm1.Show
End Sub

Reply | Quote

I want to populate them from 3 seperate finds of text- listbox1.additem string1
listbox2.additem string2
listbox3.additem string3

does that help?

Reply | Quote

The current code finds a specific word (“String”), then puts the contents of that cell and the cells below it into an array, and uses that array to populate a list box. Now, you want to populate 3 list boxes. Where do you want to populate them from? Should they all have the same contents?

By the way, instead of

For i = 0 To UBound(arrNames)
UserForm1.ListBox1.AddItem arrNames(i)
Next

you can use the single instruction

UserForm1.ListBox1.List = arrNames

Reply | Quote

OCell as range…If I were to have three seperate lists, displayed in three seperate listboxes where would I what doI need to do with this code so that I could accomplish that?

Do I need to Redim seperate ranges,

Dim arrNames()
Dim i As Long
Dim oCell As Range

Set oCell = ActiveSheet.Cells.Find(What:=”String”, LookIn:=xlValues, LookAt:=xlWhole)
If oCell Is Nothing Then
MsgBox “Value not found”, vbExclamation
Exit Sub
End If
i = 0
Do Until oCell.Offset(i, 0).Value = “”
ReDim Preserve arrNames(i)
arrNames(i) = oCell.Offset(i, 0).Value
i = i + 1
Loop
UserForm1.ListBox1.Clear
UserForm1.ListBox2.Clear —–IS this right?
UserForm1.ListBox3.Clear

For i = 0 To UBound(arrNames)
UserForm1.ListBox1.AddItem arrNames(i)
UserForm1.ListBox1.AddItem arrNames(i) ————–I want to display three different ranges, can it be done?
UserForm1.ListBox1.AddItem arrNames(i)
Next
UserForm1.Show
End Sub

Can it be done?

Thanks, Darryl.

Reply | Quote

Can you elaborate on what you want to do?

Reply | Quote

Hans Thank you —- How would I show three different strings in three different listboxs?

Reply | Quote