62640

Error 381 populating listbox from a sheet table

Question:

I am getting all the time error 381.. What is there wrong? If i use only 1 column it works, if i add 2nd and more it stops working.

I try to populate my rows which compile with "if statement".

it stops each time to work at 2nd column.

UserForm + some Data: <a href="https://drive.google.com/open?id=1hfCAu2m7C4kISSPJSvyjWc-TvxBr-fOO" rel="nofollow">https://drive.google.com/open?id=1hfCAu2m7C4kISSPJSvyjWc-TvxBr-fOO</a>

2nd Version of code:

Sub PopulateList2() Dim rngName As Range Dim ws As Worksheet Dim i As Integer Dim LastRow As Long Set ws = E1G With ListBoxAbg .Clear .ColumnCount = 2 LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row For i = 1 To LastRow If ws.Cells(i, 6).Value < Now() _ And ws.Cells(i, 6).Value <> vbNullString Then .AddItem .List(i - 1, 0) = ws.Cells(i, 1).Value .List(i - 1, 1) = ws.Cells(i, 3).Value End If Next i End With End Sub

....

Sub PopulateList2() Dim rngName As Range Dim ws As Worksheet Dim i As Integer Dim LastRow As Long Set ws = E1G AbgeListField.Clear AbgeListField.ColumnCount = 7 LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row For i = 1 To LastRow If ws.Cells(i, 6).Value < Now() _ And ws.Cells(i, 6).Value <> vbNullString Then AbgeListField.AddItem ws.Cells(i, 1).Value AbgeListField.List(i - 1, 1) = ws.Cells(i, 2).Value AbgeListField.List(i - 1, 2) = ws.Cells(i, 3).Value AbgeListField.List(i - 1, 3) = ws.Cells(i, 4).Value AbgeListField.List(i - 1, 4) = ws.Cells(i, 5).Value AbgeListField.List(i - 1, 5) = ws.Cells(i, 6).Value AbgeListField.List(i - 1, 6) = ws.Cells(i, 7).Value End If Next i End Sub

Answer1:

i found the answer in that post: <a href="https://social.msdn.microsoft.com/Forums/office/en-US/f5619db9-be72-41e3-a353-54ebb021f936/runtime-error-381-could-not-set-the-list-property-invalid-property-array-index?forum=exceldev" rel="nofollow">https://social.msdn.microsoft.com/Forums/office/en-US/f5619db9-be72-41e3-a353-54ebb021f936/runtime-error-381-could-not-set-the-list-property-invalid-property-array-index?forum=exceldev</a>

i added new dim nxtItme As Long. it works perfect now:

Sub PopulateList2() Dim rngName As Range Dim ws As Worksheet Dim i As Integer Dim LastRow As Long Dim nxtItem As Long Set ws = E1G nxtItem = 0 With ListBoxAbg .Clear .ColumnCount = 6 LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row For i = 1 To LastRow If ws.Cells(i, 6).Value < Now() _ And ws.Cells(i, 6).Value <> vbNullString Then .AddItem .List(nxtItem, 0) = ws.Cells(i, 1).Value .List(nxtItem, 1) = ws.Cells(i, 3).Value .List(nxtItem, 2) = ws.Cells(i, 4).Value .List(nxtItem, 3) = ws.Cells(i, 5).Value .List(nxtItem, 4) = ws.Cells(i, 6).Value nxtItem = nxtItem + 1 End If Next i End With End Sub

Recommend

  • What is the use of the SHT_NULL section in ELF?
  • Cannot send user message with Spring Websocket
  • Stored Procedure with multiple IN Parameter
  • Why would a Scala Worksheet using Scala-IDE give this as an error?
  • How do `pass` and `listen` work in WriterT?
  • Can I use worksheet_change for a specific column only?
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Sybase Error Implicit Conversion from datatype 'VARCHAR' to 'INT' not allowed
  • Creating a Multidimensional, Associative Array in VBScript
  • Conversion from string “a” to type 'Boolean' is not valid
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Excel's Macro-Recorder usage
  • C# - Serializing and deserializing static member
  • Problems to linebreak with an int in JLabel
  • Date difference with leap year
  • How would I use PHP exceptions to define a redirect?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • JSON with duplicate key names losing information when parsed
  • Display Images one by one with next and previous functionality
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • VBA Convert delimiter text file to Excel
  • Matplotlib draw Spline from multiple points
  • XCode can't find symbols for a specific iOS library/framework project
  • Calling of Constructors in a Java
  • Compare two NSDates in iPhone
  • Transpose CSV data with awk (pivot transformation)
  • Use group_by to filter specific cases while keeping NAs
  • How to include full .NET prerequisite for Wix Burn installer
  • log4net write single file for each call to log.info
  • Benchmarking RAM performance - UWP and C#
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • costura.fody for a dll that references another dll
  • Reading document lines to the user (python)
  • java string with new operator and a literal
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize