Yes, you will get that error if you have blank entries in your list.
You can add a check:
If cell.Text<>"" Then LBox.AddItem Split(cell.Text, DelimChar)(0)
NickHK
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> NickHK,
>
> Thanks again man but I'm still having issues...It seems your code is
> giving me the error message Run-time error '9': "Subscript out of
> range"
>
> the following code is highlighted
>
> LBox.AddItem Split(cell.Text, DelimChar)(0)
>
> What's weird is it works but still causes the error message...Will try
> a If Error Goto: 0 and see if that helps but maybe something in the
> code's syntax is wrong/missing?
>
> NickHK wrote:
> > May be this for flexibility:
> >
> > Private Sub UserForm_Click()
> > Call FillListBox(ListBox1, Worksheets("Sheet2").Range("O4:O12"), "!")
> > End Sub
> >
> > Private Function FillListBox(LBox As MSForms.ListBox, _
> > SourceRange As Range, _
> > Optional DelimChar As String = "") _
> > As Long
> > Dim cell As Range
> >
> > For Each cell In SourceRange
> > LBox.AddItem Split(cell.Text, DelimChar)(0)
> > Next
> >
> > FillListBox = LBox.ListCount
> >
> > End Function
> >
> > NickHK
> >
> > <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Seems the code I got (not from you) but from the boards isn't working
> > > right... The desired result is to do the following...
> > >
> > > 1. Take the information from Sheet1 IE2:IE200 and list it in Listbox1
> > > 2. With the values from IE2:IE200 delete everything that is the RIGHT
> > > of the "!" but only in the list box not in the spreadsheet.
> > >
> > > The values in the listbox are going to be changing so it's lot a
matter
> > > of using =RIGHT or =LEFT for that matter unless someone could provide
> > > some code to show me how to count the characters in each cell and
apply
> > > a formula in the spreadsheet (to produce the desired result) and just
> > > have the Listbox pull those values...I can get the code to work
> > > correctly then but I think in the grandshceme of things I'd be better
> > > off it the listbox was able to do this without the spreadsheet
> > > funciton. I appreciate the time you spent to get back to me NickHK, as
> > > anyone else who can help figure out this needed code. Thanks you!
> > >
> > > -Todd
> > >
> > > NickHK wrote:
> > > > I don't see the connection between the list box and the values to
> > > > edit...Maybe
> > > >
> > > > ListBox1.RowSource = "Sheet1!IE3:IE200"
> > > > Dim cell as range
> > > > Dim dblVal As Double
> > > >
> > > > for each cell in worksheets("Sheet1").range("IE3:IE200")
> > > > iloc = InStr(1, cell.value, "!")
> > > > If iloc <> 0 Then
> > > > dblVal = CDbl(Left(cell.value, iloc - 1))
> > > > Else
> > > > dblVal = 0
> > > > End If
> > > > 'Do something with dblVal
> > > > next
> > > >
> > > > If you looking to populate the list box with these editted values,
look
> > at
> > > > the .AddItem method instead of .RowSource. Or use the worksheet to
get
> > the
> > > > list entries in the correct form first.
> > > >
> > > > NickHK
> > > >
> > > > <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Hello Fellow Programmers!
> > > > >
> > > > > ListBox1.RowSource = "Sheet1!IE3:IE200"
> > > > > Dim sStr As String
> > > > > Dim dblVal As Double
> > > > > sStr = "! "
> > > > > iloc = InStr(1, sStr, "%")
> > > > > If iloc <> 0 Then
> > > > > dblVal = CDbl(Left(sStr, iloc - 1))
> > > > > Else
> > > > > dblVal = 0
> > > > > End If
> > > > >
> > > > > The idea is to have the Listbox pull the information from Sheet1
> > > > > IE3:E200 and delete every piece of text after the "!". Now the
huge
> > gap
> > > > > is because I didn't know how to accomadate the code to look for
what
> > to
> > > > > delete after the "!". Any help is much appreciated...I'd like this
to
> > > > > work with the Userform_Activate. Thanks!
> > > > >
> > >
>
|
Similar Threads
