Run-Time Error '1004' ??????

[spewbanks]

I am in the beginning stages of coding together an excel project to search and find specific data. I introduced my first WHILE statement and now I get a message box that says: "RUN-TIME Error '1004' Application-Defined or Object-Defined Error"

Here is my code:

Private Sub CommandButton1_Click()
    Dim strNumEntry As String
    If Sheet1.RMANumOption.Value = True Then
        strNumEntry = Sheet1.TextBox1.Text
        Sheet1.TextBox2.Text = strNumEntry
        Dim intRowCount As Integer
        Dim intRowNum As Integer
        Dim intColNum As Integer
        intRowNum = 1
        intColNum = 0
        intRowCount = Sheet3.UsedRange.Rows.Count
        
        While intRowNum <= intRowCount
            If Sheet1.TextBox1.Text = Sheet3.Cells(intRowNum, intColNum).Value Then
                Sheet1.TextBox2.Text = "Found It"
                intRowNum = intRowCount + 1
            Else
                intRowNum = intRowNum + 1
            End If
        Wend
        
    ElseIf Sheet1.HISSNOption.Value = True Then
        strNumEntry = Sheet1.TextBox1.Text
        Sheet1.TextBox2.Text = strNumEntry
    ElseIf Sheet1.ManSNOption.Value = True Then
        strNumEntry = Sheet1.TextBox1.Text
        Sheet1.TextBox2.Text = strNumEntry
    Else
        Sheet1.TextBox2.Text = "Error: Please choose search type"
    End If
End Sub

Any ideas?

Sorry if i did not post correctly, I am new to this and hope I tagged my code correctly.

 

[SkipVought]

Hi,

The Integer data type can hold up to 32,767, which row counts can far exceed. Rather, use the Long data type...

        Dim lRowCount As Long
        Dim lRowNum As Long

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I get a message box that says: "RUN-TIME Error '1004' Application-Defined or Object-Defined Error
And which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MakeItSo]

Howdy!

If Sheet1.RMANumOption.Value = True Then

what IS "Sheet1"?

You mean "ActiveWorkbook.Sheets("Sheet1")" instead?

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[GlennUK]

Howdy!


CODE
If Sheet1.RMANumOption.Value = True Then
what IS "Sheet1"?

You mean "ActiveWorkbook.Sheets("Sheet1")" instead?

Cheers,
MakeItSo

Hi MakeItSo, it's the sheet code name, to access the sheet object directly. A lot safer to use than your suggestion if there's a chance the user will rename the sheet.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[spewbanks]

Hi all, thanks for the replies.

I changed all my variables to Long just in case, with no change.

Here is the code that debug highlights right before I get the error:

            If Sheet1.TextBox1.Text = Sheet3.Cells(lngRowNum, lngColNum).Value Then

Thanks for all your help.

 

[GlennUK]

And is lngColNum zero, by any chance?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MakeItSo]

Hi MakeItSo, it's the sheet code name, to access the sheet object directly. A lot safer to use than your suggestion if there's a chance the user will rename the sheet.

I see!
==>Works not in localised Excels!
This for example throws an error for me:

Sub test()

Dim r As Range
Set r = sheet1.Cells(1, 1)
    
End Sub

I have to use "Tabelle1.Cells" instead!

How about you Spewbanks? Using an English Excel or a localised one?


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[spewbanks]

Glenn, Thank you my column index number was zero. I assumed you started with zero when counting columns. Thanks for your help. Everything works as expected now.

Also MakeItSo, I am using an English Excel. Thanks for everyone's input.