PDA

View Full Version : [SOLVED] Run-time error 424: Object required



onmyway
02-09-2015, 03:41 AM
hi guys,

i hope you can help.

I get a run-time error 424: Object required in the following line of my search function (down below):


GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1

How it works:


i type my reference in the TextBox (txtSearch), which then lists possible matches in ListBox3.
i then double click on the preferred record in ListBox3 to populate my UserForm. it is at this stage that I get the error....



'SEARCH FUNCTION
Private Sub txtSearch_Change()
Dim rng As range, e
Dim Ans As Long
With Me
.ListBox3.Clear
If Len(.txtSearch.Value) Then
For Each e In Sheets("DataBase").Cells(1).CurrentRegion.Columns(133).Offset(1).Value
If (e <> "") * (e Like "*" & .txtSearch.Value & "*") Then
.ListBox3.AddItem e
End If
Next
With .ListBox3
If .ListCount > 0 Then .ListIndex = 0
If .ListCount = 0 Then Ans = MsgBox("No Search reults found, continue?", vbYesNo)
If Ans = vbYes Then

If MsgBox("Would you like to create a new record?", vbYesNo) = vbYes Then

Call ActiveControls

End If

'Call btnNew_Click
Me.cbxEquipmentCommon.SetFocus

End If

End With

End If

End With

End Sub


'SEARCH FUNCTION
Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


Dim RowCount As Long
Dim numberOfColumns As Integer
numberOfColumns = 9

DetermineLastRow
'Clear the autofilter to ensure that our last row index remains corret
'cells.AutoFilter
'Get the last row to edit (this is for adding new rows when saving
'LastRowIndex = cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).row


With Sheets("DataBase").AutoFilterMode = False
With range("A1")
.AutoFilter
.AutoFilter field:=1, Criteria1:=ListBox3.Value
End With
'Get the corret row count
GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1

With UserForm1

Dim FirstRowAdd As String
Dim LastRow As Long, FirstRow As Long

FirstRowAdd = FirstVisibleValue(ActiveSheet, 1)
FirstRow = range(FirstRowAdd).row

.GUID.Value = Sheets("DataBase").Cells(FirstRow, 1).Value
' Me.TextBox19.Value = Sheets("DataBase").Cells(FirstRow, 2).Value
' Me.TextBox20.Value = Sheets("DataBase").Cells(FirstRow, 3).Value
' Me.TextBox21.Value = Sheets("DataBase").Cells(FirstRow, 4).Value


End With


End With

'Work on all the data in this section
Dim localData()
ReDim Preserve localData(GlobalRowCount - 1, numberOfColumns)

Dim currentRow As Integer
currentRow = 0

Dim currentCell As Integer
currentCell = 0

For Each row In range("RecordData").SpecialCells(xlCellTypeVisible).Rows

If (currentRow > GlobalRowCount) Then Exit For

'We should ignore the filter header
If (currentRow > 0) Then

Dim thisRange As range
Set thisRange = row.Cells.SpecialCells(xlCellTypeVisible)

Dim currentReference As RowReference
Set currentReference = New RowReference
currentReference.AllCells = thisRange
currentReference.CellCount = thisRange.count
currentReference.IsNew = False
currentReference.RowIndex = thisRange.row

Set GlobalData(currentRow - 1) = currentReference

Dim cellIndex As Integer
cellIndex = 0
For Each cell In thisRange
localData(currentRow - 1, cellIndex) = cell
cellIndex = cellIndex + 1
Next cell

End If
currentRow = currentRow + 1
Next row


'update list box
ListBox1.ColumnCount = numberOfColumns
ListBox1.Clear
ListBox1.List() = localData

End Sub

Aflatoon
02-09-2015, 03:54 AM
This line:

With Sheets("DataBase").AutoFilterMode = False

needs to be two lines:

With Sheets("DataBase")
.AutoFilterMode = False

Also cross-posted here: http://www.mrexcel.com/forum/excel-questions/834611-run-time-error-424-object-required.html

onmyway
02-09-2015, 04:10 AM
Hi Aflatoon,

thanks for the help!

I now get an error (run-time error 9: subscript out of range) on this line:


ReDim Preserve localData(GlobalRowCount - 1, numberOfColumns)

In the same section of the code above (my first post), just a bit down.


'Work on all the data in this section
Dim localData()
ReDim Preserve localData(GlobalRowCount - 1, numberOfColumns)

Dim currentRow As Integer
currentRow = 0

Dim currentCell As Integer
currentCell = 0

For Each row In range("RecordData").SpecialCells(xlCellTypeVisible).Rows

If (currentRow > GlobalRowCount) Then Exit For

'We should ignore the filter header
If (currentRow > 0) Then

Dim thisRange As range
Set thisRange = row.Cells.SpecialCells(xlCellTypeVisible)

Dim currentReference As RowReference
Set currentReference = New RowReference
currentReference.AllCells = thisRange
currentReference.CellCount = thisRange.count
currentReference.IsNew = False
currentReference.RowIndex = thisRange.row

Set GlobalData(currentRow - 1) = currentReference

Dim cellIndex As Integer
cellIndex = 0
For Each cell In thisRange
localData(currentRow - 1, cellIndex) = cell
cellIndex = cellIndex + 1
Next cell

End If
currentRow = currentRow + 1
Next row


'update list box
ListBox1.ColumnCount = numberOfColumns
ListBox1.Clear
ListBox1.List() = localData

End Sub


PS: i did post the question on more than one forum (mrexcel), are the 2 sites related?

Aflatoon
02-09-2015, 04:30 AM
No, they are not related but if you read the forum rules on either you will see the etiquette for cross-posting, and the rationale behind it.

If you use the Preserve keyword when resizing an array, you can only alter the upper boundary of the last dimension of the array.

onmyway
02-09-2015, 04:34 AM
Noted (cross-posting) - my apologies.

Could you perhaps explain or give me a possible solution:
If you use the Preserve keyword when resizing an array, you can only alter the upper boundary of the last dimension of the array.

i am relatively new to VBA!

Thank you!

Aflatoon
02-09-2015, 04:48 AM
You have a 2D array. If you use Preserve, you can't resize the first dimension - only the last (second) one. I can't see any reason for using Preserve in what you posted so I suggest you just remove it.

onmyway
02-09-2015, 05:03 AM
Would you mind taking a look at my workbook for me. Would really appreciate it!

The search function with ListBox is on the left hand side of the UserForm.

To do a search, you can type in the field that shows Identity Number. Type "DLH", and dbl click on any of the ListBox items to replicate the error.

Aflatoon
02-09-2015, 05:10 AM
You need:
1. A range called RecordData which your code refers to, but which doesn't exist.

2.
ReDim localData(GlobalRowCount, numberOfColumns)

3. You also need to check that something matches your filter!

onmyway
02-09-2015, 06:31 AM
Hi Aflatoon,

Made the suggested changes. Thank you!

The named range: RecordData - complete oversight!
I do get a match for my filter, but when a dbl click my ListBox item, it does not populate my UserForm, and I also do not get an error...:thinking:

This is my latest workbook if you wouldn't mind taking a look.

Aflatoon
02-09-2015, 06:46 AM
You're filtering column A for the value in the listbox but it's actually in column EC.

onmyway
02-09-2015, 07:08 AM
you rock! thank you very much!