Can you explain something in my code please

[ipbr21054]

Hi,
I was getting the RTE 1004 Method range of object global failed.

When i then clicked on debug this line of code in Red was shown in yellow.

Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 3)).Select
  Unload PostalIssueForm
End Sub

Please can you explain what the digit 3 represents.

This is also the other code for the page.

Private Function add_val(a As String)

      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        
        .ColumnCount = 4
        .ColumnWidths = "180;230;250;10"

        Set r = Range("G8", Range("G" & Rows.Count).End(xlUp))
        
        Set f = r.Find(a, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
              .AddItem f.Value, i                 'DATE / POSTAL ISSUE COLUMN
              .List(i, 1) = f.Offset(, -5).Value  'NAME
              .List(i, 2) = f.Offset(, -4).Value  'ITEM
              .List(i, 3) = f.Offset(, -6).Value  'DATE
              .List(i, 4) = f.Row                 'ROW
              added = True
              Exit For
          End Select

            Next
            If added = False Then
          .AddItem f.Value                                 'DATE / POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          ComboBox1 = UCase(ComboBox1)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          ComboBox1.Value = ""
          .SetFocus
        End If
      End With
End Function
Private Sub UserForm_Initialize()

Call add_val("LOST")
Call add_val("RECEIVED NO DATE")
Call add_val("RETURNED")
Call add_val("UNKNOWN")
End Sub

What is supposed to happen is that when the code is run the text in Bold above will be searched for in column G and all matches will then be placed in ListBox1
The ListBox1 has 4 columns of values/text etc.

When i then select a value in ListBox1 i am then taken to that value on the worksheet.
The cell in column A is then selected for that value.

It is when i make the selection on the ListBox1 that the RTE is shown

I ask this question because to see what the issue was i changed the 3 to a 1 and run the code again.
I kept getting this error untill 4 was entered then the RTE wasnt shown anymore.

Now i need to know please why or what does it represent.

Thanks

 

[nemmi69]

Well the 3 should not be there. Also "ListBox1.List(ListBox1.ListIndex" would return you the content of the listbox. The +1 is because index starts at 0.

Private Sub ListBox1_Click()
  Range("A" & ListBox1.ListIndex+1).Select
  Unload PostalIssueForm
End Sub

 

[ipbr21054]

That cant be right.
The code i supplied is when i click a value say TOM JONES in the ListBox1 i am then taken to TOM JONES on the worksheet.

With your code in place i click TOM JONES but taken to say BOB MONKHOUSE.

Also i still dont know what the 3 is for ?

 

[Fluff]

You have a 5 column listbox, not 4 & the 3 means the 4th col (it's indexed from 0), so as the row number is in the 5th column you should use 4 & not 3

 

[ipbr21054]

So to understand that.
i is the 1st row & Row is the last row ?

              .AddItem f.Value, i                 'DATE / POSTAL ISSUE COLUMN
              .List(i, 1) = f.Offset(, -5).Value  'NAME
              .List(i, 2) = f.Offset(, -4).Value  'ITEM
              .List(i, 3) = f.Offset(, -6).Value  'DATE
              .List(i, 4) = f.Row                 'ROW

So the 3 we mention refers to the 1,2,3,4 shown above.

 

[Fluff]

They are columns not rows.

So the 3 we mention refers to the 1,2,3,4 shown above

That's right as f.value is in the 1st column which is index 0

 

[ipbr21054]

Row was meaning this .AddItem f.Value, i 'DATE / POSTAL ISSUE COLUMN on the post not column of the worksheet.

But now i understand.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.