Also have the row number shown in Listbox on userform advice

[ipbr21054]

The following code works fine but im unable to write the code correctly so the row num,ber is also shown in the Listbox

Private Sub UserForm_Initialize()

    Dim fndRng As Range
    Dim firstAddress As String
    Dim cnt As Long
    Dim elapsedDays As Long
    
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;195;110;170;130;50;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do ' check the date
           elapsedDays = Date - DateValue(fndRng.Offset(, -6))
            If elapsedDays <= 80 And elapsedDays >= 30 Then
            
                cnt = cnt + 1
                With Me.ListBox1 ' ADD VALUES TO LISTBOX
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER'S NAME
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG
                    .List(.ListCount - 1, 7) = fndRng.Row.Offset(, -2).Value
                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

If cnt = 0 Then
    MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
    End
End If
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 100  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

 

[Skyybot]

Private Sub UserForm_Initialize()

Dim fndRng As Range
Dim firstAddress As String
Dim cnt As Long
Dim elapsedDays As Long

With Me.ListBox1
.ColumnCount = 8
.ColumnWidths = "220;195;110;170;130;50;100;50"
End With

With Sheets("POSTAGE").Range("G:G")
Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
firstAddress = fndRng.Address
Do ' check the date
elapsedDays = Date - DateValue(fndRng.Offset(, -6))
If elapsedDays <= 80 And elapsedDays >= 30 Then

cnt = cnt + 1
With Me.ListBox1 ' ADD VALUES TO LISTBOX
.AddItem fndRng.Offset(, -5).Value 'CUSTOMER'S NAME
.List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value 'ITEM
.List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value 'TRACKING NUMBER
.List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value 'CLAIM
.List(.ListCount - 1, 6) = fndRng.Value 'RECEIVED NO SIG
.List(.ListCount - 1, 7) = fndRng.Row.Offset(, -2).Value
.List(.ListCount - 1,8) = fndRng.Row
End With
End If
Set fndRng = .FindNext(fndRng)
Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
End If
End With

If cnt = 0 Then
MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
End
End If
Me.StartUpPosition = 0
Me.Top = Application.Top + 100 ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

 

[ipbr21054]

I get a compile error when pressing the green arrow in the vba page

 

[Skyybot]

fndRng.Offset(,-2).Value

 

[ipbr21054]

There are 2 lines of code the same

 

[Skyybot]

There are 2 lines of code the same

You are correct. Those were already in your code. I only added

.List(.ListCount - 1,8) = fndRng.Row

 

[Skyybot]

Try this

Private Sub UserForm_Initialize()

Dim fndRng As Range
Dim firstAddress As String
Dim cnt As Long
Dim elapsedDays As Long

With Me.ListBox1
.ColumnCount = 7
.ColumnWidths = "220;195;110;170;130;50;100"
End With

With Sheets("POSTAGE").Range("G:G")
Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
firstAddress = fndRng.Address
Do ' check the date
elapsedDays = Date - DateValue(fndRng.Offset(, -6))
If elapsedDays <= 80 And elapsedDays >= 30 Then

cnt = cnt + 1
With Me.ListBox1 ' ADD VALUES TO LISTBOX
.AddItem fndRng.Offset(, -5).Value 'CUSTOMER'S NAME
.List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value 'ITEM
.List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value 'TRACKING NUMBER
.List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value 'CLAIM
.List(.ListCount - 1, 6) = fndRng.Value 'RECEIVED NO SIG
.List(.ListCount - 1,7) = fndRng.Row
End With
End If
Set fndRng = .FindNext(fndRng)
Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
End If
End With

If cnt = 0 Then
MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
End
End If
Me.StartUpPosition = 0
Me.Top = Application.Top + 100 ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

 

[NoSparks]

@ipbr21054
I believe your posted code originated here where the list box columns are populated consecutively.
But you now miss the sixth column, perhaps that is intentional, but if not, all that needs changed in your post 1 code is

                    .List(.ListCount - 1, 5) = fndRng.Value                 'RECEIVED NO SIG
                    .List(.ListCount - 1, 6) = fndRng.Row

(and perhaps column width adjustment)