Error In Code??

[Mas]

I have the folowing code to get missing serial from a
sheet and i get error on j=c.value

i want to get serial breake on a filtered range

Private Sub UserForm_Activate()
Dim Sh As Worksheet
Dim i As Long, j As Long
Dim Rng1 As Range
Application.ScreenUpdating = False

Me.Caption = Sheets("Interface").Range("C1")
N = Me.Caption
Set Sh = Sheets("AllSubsData")
Sh.Select
ActiveSheet.Unprotect Password:="pswd"
Sh.AutoFilterMode = False

Range(Range("I1"), Range("A65536").End(xlUp)).Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

TextBox1.Text = Sh.Range("C1").Offset(1, 0).Value
TextBox2.Text = Sh.Range("C65536").End(xlUp).Value

ListBox1.Clear
Range("A1").AutoFilter

With [_FilterDatabase].Offset(1, 0)
Range("A1").AutoFilter Field:=7, Criteria1:=N
Set Rng1 = .SpecialCells(xlCellTypeVisible)

i = 1

For Each c In Rng1
If c.Offset(1).Value = "" Then Exit For
If c.EntireRow.Hidden = False Then
j = c.Value
Do Until j = c.Offset(1).Value - 1
j = j + 1: i = i + 1
k = ListBox1.ListCount
ListBox1.AddItem j

Loop
End If
Next

End With


If k = 0 Then ListBox1.AddItem "Not Found"
Sh.AutoFilterMode = False
Sh.Protect Password:="pswd"

End Sub

Any Help??

TIA
Mas

 

[Frank Kabel]

Hi
without looking to much at your code: Is c.value alsway a long?
Try diming j as variant

 

[Mas]

Y the loop do not stop at the end of the cell...

it continues and generate an error?

-----Original Message-----
Hi
without looking to much at your code: Is c.value alsway a long?
Try diming j as variant

--
Regards
Frank Kabel
Frankfurt, Germany

I have the folowing code to get missing serial from a
sheet and i get error on j=c.value

i want to get serial breake on a filtered range

Private Sub UserForm_Activate()
Dim Sh As Worksheet
Dim i As Long, j As Long
Dim Rng1 As Range
Application.ScreenUpdating = False

Me.Caption = Sheets("Interface").Range("C1")
N = Me.Caption
Set Sh = Sheets("AllSubsData")
Sh.Select
ActiveSheet.Unprotect Password:="pswd"
Sh.AutoFilterMode = False

Range(Range("I1"), Range("A65536").End(xlUp)).Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

TextBox1.Text = Sh.Range("C1").Offset(1, 0).Value
TextBox2.Text = Sh.Range("C65536").End(xlUp).Value

ListBox1.Clear
Range("A1").AutoFilter

With [_FilterDatabase].Offset(1, 0)
Range("A1").AutoFilter Field:=7, Criteria1:=N
Set Rng1 = .SpecialCells(xlCellTypeVisible)

i = 1

For Each c In Rng1
If c.Offset(1).Value = "" Then Exit For
If c.EntireRow.Hidden = False Then
j = c.Value
Do Until j = c.Offset(1).Value - 1
j = j + 1: i = i + 1
k = ListBox1.ListCount
ListBox1.AddItem j

Loop
End If
Next

End With


If k = 0 Then ListBox1.AddItem "Not Found"
Sh.AutoFilterMode = False
Sh.Protect Password:="pswd"

End Sub

Any Help??

TIA
Mas

.