Getting RunTime Error 1004

[get2noesks]

Hi,

I am trying to make a collection of rows and columns on a successful search of the text found. While executing the code I get a run time error message "Run-time error '1004' Application Defined or object defined error".

Code is as followed:

Function getValuesToSort() As Collection
Dim findString As String
Dim cntrlRow, cntrlCol As Integer
Dim WS1 As Worksheet
Dim WS2 As Worksheet

Dim var As Collection
Set var = New Collection

Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")

cntrlRow = 3
cntrlCol = 2

Dim iRow, iCol, i, j As Integer

iRow = iCol = 1

While WS2.Cells(cntrlRow, cntrlCol) <> ""
findString = WS2.Cells(cntrlRow, cntrlCol)
While WS1.Cells(iRow, iCol).Value <> ""
If findString = WS1.Cells(iRow, iCol) Then
var.Add (iRow)
var.Add (iCol)
End If
MsgBox var
iCol = iCol + 1
Wend
cntrlRow = cntrlRow + 1
Wend
Set getValuesToSort = var
End Function


Please advise.

 

[mole999]

please use CODE tags to display code, tell us where the code fails. 1004 normally means that something you expect to be referenced isn't obvious to the code, a sheetname or similar

 

[GTO]

Hi there,

I am not exactly following, but did notice that where you have:

Dim iRow, iCol, i, j As Integer

iRow, iCol and I are being declared as Variants. To have them as Integers (which I would suggest simply declaring them as Longs), it needs to be like this:

Dim iRow As Integer, iCol As Integer, i As Integer, j As Integer

As you have things currently declared and because of:

iRow = iCol = 1

...at this line...

[font=Courier New][color=darkblue]While[/color] WS1.Cells(iRow, iCol).Value <> ""[/font]

...iRow has been sub-typed to a Boolean and iCol is Empty, so it falls over.

With the variables all dim'ed to Long (or Integer), iRow will return 0 at this point, as where iRow is assigned the return of iCol = 1 (which it doesn't, as iCol currently is 0), so iRow will be zero after converting the FALSE return to it's numeric equivalent (which I just misspelled). This will of course still fall over, as Row or Column being 0 will fail.

Anyways, hope that helps a little at least,

Mark

 

[get2noesks]

Thanks. It helped.