Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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 wrote in message oups.com... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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 wrote in message oups.com... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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 wrote in message ups.com... 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 wrote in message oups.com... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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 wrote in message ups.com... 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 wrote in message oups.com... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
NickHK,
Thanks man your code works great...Just needed to add that error handler and it worked fine. I'll remember this and hopefully some day I can return the favor. Thanks alot man! -Todd wrote: 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 wrote in message ups.com... 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 wrote in message oups.com... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this happen?
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 wrote in message oups.com... 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 wrote in message ups.com... 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 wrote in message oups.com... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wow... Anyone ever seen this happen? | Excel Discussion (Misc queries) | |||
How do I make this happen | Excel Worksheet Functions | |||
Why is that happen ? | Excel Worksheet Functions | |||
Cell Change activating macro. How do you make it happen? | Excel Programming | |||
Can I make some thing happen if I get a "reminder" | Excel Programming |