listbox issue
 

Good day gents.
Hopefully you with your great experience can help me for the following
vba issue.
I have an excel file with an userform who has a listbox and a sheet
with some records.
Those records have a string on first column and correspondly a score
(integer) on the second column
What I want to do by vba into the userform is to sum the content of
those records column A (showing the result into the listbox) until
the characthers of "string sum" is max 21 character including the
space between the records.
For example I have data (column A) with score (column b) into sheets3
like follows
A B
First 12
Second 9
Third 4
..
..
fiftheenth 10
..
twentyth 9

macro has to sum starting from "First" down until the string sum (for
example "first second fiftheen") characters are "21", so showing this
result into listbox into the userform and related sum score (in this
example score = 31) in the same listbox.
Sheet3 contains 1000 records so macro ha to repeat the same loop to
find as much as string sum possible with this criteria (I remember the
sum of character max 21) showing all to the listbox a.m.

Thanks so much for your help

listbox issue
 

Not sure I follow 100%, but this should get you started:

Private Sub UserForm_Click()
Dim Total As Long
Dim Cell As Range

With Worksheets(1)
For Each Cell In .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
If Len(Cell.Value) < 22 Then
ListBox1.AddItem Cell.Value
Total = Total + Cell.Offset(0, 1).Value
'Debug.Print Cell.Offset(0, 1).Value, Total
Else
Exit For
End If
Next
End With

MsgBox "Total=" & Total

End Sub

NickHK

wrote in message
oups.com...
Good day gents.
Hopefully you with your great experience can help me for the following
vba issue.
I have an excel file with an userform who has a listbox and a sheet
with some records.
Those records have a string on first column and correspondly a score
(integer) on the second column
What I want to do by vba into the userform is to sum the content of
those records column A (showing the result into the listbox) until
the characthers of "string sum" is max 21 character including the
space between the records.
For example I have data (column A) with score (column b) into sheets3
like follows
A B
First 12
Second 9
Third 4
.
.
fiftheenth 10
.
twentyth 9

macro has to sum starting from "First" down until the string sum (for
example "first second fiftheen") characters are "21", so showing this
result into listbox into the userform and related sum score (in this
example score = 31) in the same listbox.
Sheet3 contains 1000 records so macro ha to repeat the same loop to
find as much as string sum possible with this criteria (I remember the
sum of character max 21) showing all to the listbox a.m.

Thanks so much for your help

listbox issue
 

On 12 Mar, 10:07, "NickHK" wrote:
Not sure I follow 100%, but this should get you started:

Private Sub UserForm_Click()
Dim Total As Long
Dim Cell As Range

With Worksheets(1)
For Each Cell In .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
If Len(Cell.Value) < 22 Then
ListBox1.AddItem Cell.Value
Total = Total + Cell.Offset(0, 1).Value
'Debug.Print Cell.Offset(0, 1).Value, Total
Else
Exit For
End If
Next
End With

MsgBox "Total=" & Total

End Sub

NickHK

wrote in message

oups.com...



Good day gents.
Hopefully you with your great experience can help me for the following
vbaissue.
I have an excel file with an userform who has alistboxand a sheet
with some records.
Those records have a string on first column and correspondly a score
(integer) on the second column
What I want to do by vba into the userform is to sum the content of
those records column A (showing the result into thelistbox) until
the characthers of "string sum" is max 21 character including the
space between the records.
For example I have data (column A) with score (column b) into sheets3
like follows
A B
First 12
Second 9
Third 4
.
.
fiftheenth 10
.
twentyth 9

macro has to sum starting from "First" down until the string sum (for
example "first second fiftheen") characters are "21", so showing this
result intolistboxinto the userform and related sum score (in this
example score = 31) in the samelistbox.
Sheet3 contains 1000 records so macro ha to repeat the same loop to
find as much as string sum possible with this criteria (I remember the
sum of character max 21) showing all to thelistboxa.m.

Thanks so much for your help- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

what i want to do is to sum the string until the tot characters is not
over than 21 and to show the string "sum" into the listbox, pls help

listbox issue
 

Why show a single value in a list box ?

Also, what does this mean ?
"sum the string until the tot characters is not over than 21"

You want to sum the values in column B ?
I do not see how you you achieve you example:" (in this example score = 31)

NickHK

"ancora" wrote in message
ups.com...
On 12 Mar, 10:07, "NickHK" wrote:
Not sure I follow 100%, but this should get you started:

Private Sub UserForm_Click()
Dim Total As Long
Dim Cell As Range

With Worksheets(1)
For Each Cell In .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
If Len(Cell.Value) < 22 Then
ListBox1.AddItem Cell.Value
Total = Total + Cell.Offset(0, 1).Value
'Debug.Print Cell.Offset(0, 1).Value, Total
Else
Exit For
End If
Next
End With

MsgBox "Total=" & Total

End Sub

NickHK

wrote in message

oups.com...



Good day gents.
Hopefully you with your great experience can help me for the following
vbaissue.
I have an excel file with an userform who has alistboxand a sheet
with some records.
Those records have a string on first column and correspondly a score
(integer) on the second column
What I want to do by vba into the userform is to sum the content of
those records column A (showing the result into thelistbox) until
the characthers of "string sum" is max 21 character including the
space between the records.
For example I have data (column A) with score (column b) into sheets3
like follows
A B
First 12
Second 9
Third 4
.
.
fiftheenth 10
.
twentyth 9

macro has to sum starting from "First" down until the string sum (for
example "first second fiftheen") characters are "21", so showing this
result intolistboxinto the userform and related sum score (in this
example score = 31) in the samelistbox.
Sheet3 contains 1000 records so macro ha to repeat the same loop to
find as much as string sum possible with this criteria (I remember the
sum of character max 21) showing all to thelistboxa.m.

Thanks so much for your help- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

what i want to do is to sum the string until the tot characters is not
over than 21 and to show the string "sum" into the listbox, pls help

listbox issue
 

On 14 Mar, 09:00, "NickHK" wrote:
Why show a single value in a list box ?

Also, what does this mean ?
"sumthestringuntilthe tot characters is not over than 21"

You want tosumthe values in column B ?
I do not see how you you achieve you example:" (in this example score = 31)

NickHK

"ancora" wrote in message

ups.com...



On 12 Mar, 10:07, "NickHK" wrote:
Not sure I follow 100%, but this should get you started:

Private Sub UserForm_Click()
Dim Total As Long
Dim Cell As Range

With Worksheets(1)
For Each Cell In .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
If Len(Cell.Value) < 22 Then
ListBox1.AddItem Cell.Value
Total = Total + Cell.Offset(0, 1).Value
'Debug.Print Cell.Offset(0, 1).Value, Total
Else
Exit For
End If
Next
End With

MsgBox "Total=" & Total

End Sub

NickHK

wrote in message

roups.com...

Good day gents.
Hopefully you with your great experience can help me for the following
vbaissue.
I have an excel file with an userform who has alistboxand a sheet
with some records.
Those records have astringon first column and correspondly a score
(integer) on the second column
What I want to do by vba into the userform is tosumthe content of
those records column A (showing the result into thelistbox)until
the characthers of "stringsum" is max 21 character including the
space between the records.
For example I have data (column A) with score (column b) into sheets3
like follows
A B
First 12
Second 9
Third 4
.
.
fiftheenth 10
.
twentyth 9

macro has tosumstarting from "First" downuntilthestringsum(for
example "first second fiftheen") characters are "21", so showing this
result intolistboxinto the userform and relatedsumscore (in this
example score = 31) in the samelistbox.
Sheet3 contains 1000 records so macro ha to repeat the same loop to
find as much asstringsumpossible with this criteria (I remember the
sumof character max 21) showing all to thelistboxa.m.

Thanks so much for your help- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

what i want to do is tosumthestringuntilthe tot characters is not
over than 21 and to show thestring"sum" into the listbox, pls help- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

let me explain better, and forget column B for the moment
the example string (i.e. "first second fiftheen") has 21 characters
including spaces between the words (you can count for verify it)
what i would like to do is macro to sum the string in column A,
starting from the first word (for example "first") and going down to
sum the other strings until the string "sum" like example "first
second fiftheen" is found and the characters are 21.
For sure there will be other possibility to find words for which the
sum has 21 characters, then macro has to put this second result in the
cell immediately after the first one, where, for example, macro
already put the first result, i.e. "first second fiftheen" string with
21 characters.
We can leave the listbox, it's sufficient to show results on column C
cells
The purpose is to find all the words combination with 21 characters
and put those results on column C

thanks again for your precious support
I'll appreciate it