Solved: Why this code not works?

[Rayman]

I have a ListBox with 4 columns,
I fill the first 3 columns with data taken from worksheet range
In column 4 i want write the date, simple no??

ListBox1.ColumnCount = 3
ListBox1.List = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
For i = 0 To ListBox1.ListCount - 1
ListBox1.List(i, 3) = Date
Next i

The above code is not working and i am out of idea.

Hope in your help.: pray2:

Thanks in advance

[GTO]

I am afraid you have only three columns. It gets confusing doesn't it? .ColumnCount is literal or "one-based" if you will. If you want 4 columns, that is what to specify.

[Bob Phillips]

ListBox1.ColumnCount = 4
ListBox1.List = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
For i = 0 To ListBox1.ListCount - 1
ListBox1.List(i, 4) = Format(Date , "dd/mm/yyyy")
Next i

[Rayman]

I am afraid you have only three columns. It gets confusing doesn't it? .ColumnCount is literal or "one-based" if you will. If you want 4 columns, that is what to specify.

Yes Gto, thanks for reply
my mistake, but the code is not working also with ColumnCount=4

[Rayman]

ListBox1.ColumnCount = 4
ListBox1.List = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
For i = 0 To ListBox1.ListCount - 1
ListBox1.List(i, 4) = Format(Date , "dd/mm/yyyy")
Next i

Xld, thanks for reply, you and Gto have helped me on many occasions...

ill try your code but its not working...

[Rayman]

Xld, thanks for reply, you and Gto have helped me on many occasions...

ill try your code but its not working...

This is the error message :"Impossible to set the list value of the property is not valid"

[shrivallabha]

The point to consider is: you have defined 3 Sheet columns, H, I & J and you are trying to assign the 4th column through VBA. Probably thats where it is not working.

Maybe you should create an array through the range and then pass it to the listbox1 like:
Dim vListArray As Variant
Set sht = Sheets(1)
ListBox1.ColumnCount = 4
sht.Range("K1:K" & sht.Range("H" & Rows.Count).End(xlUp).Row).Value = Date
vListArray = sht.Range("H1:K" & sht.Range("H" & Rows.Count).End(xlUp).Row).Value
ListBox1.List = vListArray

And then delete Range(K) data.

[Rayman]

The point to consider is: you have defined 3 Sheet columns, H, I & J and you are trying to assign the 4th column through VBA. Probably thats where it is not working.

Maybe you should create an array through the range and then pass it to the listbox1 like:
Dim vListArray As Variant
Set sht = Sheets(1)
ListBox1.ColumnCount = 4
sht.Range("K1:K" & sht.Range("H" & Rows.Count).End(xlUp).Row).Value = Date
vListArray = sht.Range("H1:K" & sht.Range("H" & Rows.Count).End(xlUp).Row).Value
ListBox1.List = vListArray

And then delete Range(K) data.

Thanks shrivallabha,
You are right i also have thinks to solve in your way, but i dont like write and cancel data for speed of code , but yes , your solution works.

Many thanks

oh, and its not the first time yuo give me a working solution, remeber the Pivot table?...

Many , many thanks:hi:

[GTO]

Hey shrivallabha,

Nicely spotted!

I stepped thru this:
Dim a
a = Me.ListBox1.List
Me.ListBox1.ColumnCount = 1
a = Me.ListBox1.List
Me.ListBox1.ColumnCount = 4
a = Me.ListBox1.List
Me.ListBox1.List = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
a = Me.ListBox1.List



It appears to me that the listbox initializes at null, stays null if only 1 column (no vals added) changes to 0 to -1, 0 to 9 when we specify 4 columns (still no vals added), but if we plunk the range(.Value) into the .List, it resizes 0 to (rows in range minus 1), 0 to 2!

Anyways, here is what I came up with (though if the next column is available, that sounds speedy).

ListBox1.ColumnCount = 4
Set rng = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row)
For x = 1 To UBound(rng.Value, 1)
Me.ListBox1.AddItem rng(x, 1).Value
For y = 2 To UBound(rng.Value, 2)
Me.ListBox1.List(x - 1, y - 1) = rng(x, y).Value
Next
Next
For i = 0 To ListBox1.ListCount - 1
ListBox1.List(i, 3) = Format(Date, "dd/mm/yyyy")
Next i

[Rayman]

Hey shrivallabha,

Nicely spotted!

I stepped thru this:
Dim a
a = Me.ListBox1.List
Me.ListBox1.ColumnCount = 1
a = Me.ListBox1.List
Me.ListBox1.ColumnCount = 4
a = Me.ListBox1.List
Me.ListBox1.List = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
a = Me.ListBox1.List



It appears to me that the listbox initializes at null, stays null if only 1 column (no vals added) changes to 0 to -1, 0 to 9 when we specify 4 columns (still no vals added), but if we plunk the range(.Value) into the .List, it resizes 0 to (rows in range minus 1), 0 to 2!

Anyways, here is what I came up with (though if the next column is available, that sounds speedy).

ListBox1.ColumnCount = 4
Set rng = sht.Range("H2:J" & sht.Cells(Rows.Count, 8).End(xlUp).Row)
For x = 1 To UBound(rng.Value, 1)
Me.ListBox1.AddItem rng(x, 1).Value
For y = 2 To UBound(rng.Value, 2)
Me.ListBox1.List(x - 1, y - 1) = rng(x, y).Value
Next
Next
For i = 0 To ListBox1.ListCount - 1
ListBox1.List(i, 3) = Format(Date, "dd/mm/yyyy")
Next i


Thanks GTO, but also with your complicated code i have the same error result.
I ask to myself: but Microsoft VBA Programmers why dont add a simple way to make this thing???

Many Thanks all

[GTO]

Put a Stop above ListBox1.ColumnCount = 4. Step thru and see what line the error occurs on.

[Rayman]

Thanks GTO, but also with your complicated code i have the same error result.
I ask to myself: but Microsoft VBA Programmers why dont add a simple way to make this thing???

Many Thanks all

MY MISTAKE GTO, sorry,

i left a small part of my code using your , this cause the error i had.

Your code works perfectly, i thinks you are a GENIUS:ipray: :ipray: :ipray:

Many thanks to all helpfull people of this forum.

[shrivallabha]

MY MISTAKE GTO, sorry,

i left a small part of my code using your , this cause the error i had.

Your code works perfectly, i thinks you are a GENIUS:ipray: :ipray: :ipray:

Many thanks to all helpfull people of this forum.

Yes, Mark's answer is on the MARK. He for sure is VBAX Contributor without tag :cool: !

[GTO]

:blush Well, I am certainly no genius, but the thanks to all the helpful members is always nice to hear.

Not that its likely we'll need a list big enough to worry about code speed, but I really like Shrivallabha's idea to get the vals into .list in one swoop. It occured to me that unless we are in the last couple of columns, we could use his method without changing the vals on the sheet, by just over-sizing the array. By example:

Private Sub UserForm_Initialize()
Dim sht As Worksheet
Dim aryVals() As Variant
Dim i As Long
Dim MyDateString As String

Set sht = Sheet2
'// "Oversize" our array by one column, fix that column below//
aryVals = sht.Range("H2:K" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
MyDateString = Format(Date, "dd/mm/yyyy")

'// Overwrite any previous vals in the fourth colummn //
For i = 1 To UBound(aryVals, 1)
aryVals(i, 4) = MyDateString
Next

With Me
.ListBox1.ColumnCount = 4
'// plunk it in the listbox in one step//
.ListBox1.List = aryVals
End With
End Sub

Just food for thought, but I am very confident this would run faster than looping thru the range.

Mark

[mikerickson]

The intermediate array is not needed.
Private Sub UserForm_Initialize()
Dim dataRange As Range
Dim i As Long
Set dataRange = Sheet1.Range("H2:J" & Sheet1.Cells(Rows.Count, 8).End(xlUp).Row)

With ListBox1
.ColumnCount = 4
.List = dataRange.Resize(, 4).Value
For i = 0 To .ListCount - 1
.List(i, 3) = Date
Next i
End With
End Sub

[GTO]

The intermediate array is not needed.

:bow: Nice one and thank you Mike.

Now not to pick nits, but would you agree that Date being assigned to a variable once and the variable being used in the loop would be optimal (assuming a long range)? It might not go along with your suggested brevity, but with the optimizing part...

My testing was quick, but:

Option Explicit

Sub UseDate()
Dim v(1 To 40000) As Date
Dim n As Long
Dim Hack As Double: Hack = Timer
For n = 1 To 40000
v(n) = Date
Next
Debug.Print "use Date: " & Timer - Hack
End Sub

Sub UseVar()
Dim v(1 To 40000) As Date
Dim n As Long
Dim d As Date
Dim Hack As Double: Hack = Timer
d = Date
For n = 1 To 40000
v(n) = d
Next
Debug.Print "use variable: " & Timer - Hack
End Sub

...resulted in (and yes, on the barely electric laptop; but still, I think it holds):

use Date: 0.531476562500757
use variable: 2.95781250006257E-02
use Date: 0.501257812500626
use variable: 3.02343750008731E-02
use Date: 0.540015624999796
use variable: 3.99609374999272E-02
use Date: 0.551203125000029
use variable: 3.99765625006694E-02

I of course admit we ain't likely to be filling 40k rows on a list, and frankly, I always have to look up the scientific notation to figure out the number, but on the poor ol' laptop, I can practically count "1 Mississippi, 2 Mississippi,..." for the difference.

On a personal note, how's your weather over there? I imagine we're in for one more (maybe two) spikes, but it just cooled down here :-)

[mikerickson]

Yes a variable would be optimal, there is no need to re-calculate Date every time.

(If every item in the CB.List has the same date, why add it to every list row? would be a question I might ask.)

[Rayman]

:blush Well, I am certainly no genius, but the thanks to all the helpful members is always nice to hear.

Not that its likely we'll need a list big enough to worry about code speed, but I really like Shrivallabha's idea to get the vals into .list in one swoop. It occured to me that unless we are in the last couple of columns, we could use his method without changing the vals on the sheet, by just over-sizing the array. By example:

Private Sub UserForm_Initialize()
Dim sht As Worksheet
Dim aryVals() As Variant
Dim i As Long
Dim MyDateString As String

Set sht = Sheet2
'// "Oversize" our array by one column, fix that column below//
aryVals = sht.Range("H2:K" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
MyDateString = Format(Date, "dd/mm/yyyy")

'// Overwrite any previous vals in the fourth colummn //
For i = 1 To UBound(aryVals, 1)
aryVals(i, 4) = MyDateString
Next

With Me
.ListBox1.ColumnCount = 4
'// plunk it in the listbox in one step//
.ListBox1.List = aryVals
End With
End Sub

Just food for thought, but I am very confident this would run faster than looping thru the range.

Mark

Yes Marks , the new code is faster.

Great idea do this without looping...

Many thanks.