listbox issue

[ancora70]

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

 

[NickHK]

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

 

[ancora]

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








- 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

 

[NickHK]

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]

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







- 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