Can you explain something in my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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.


Rich (BB code):
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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.

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Rich (BB code):
              .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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top