How to Avoid Run Time Error 1004 without using On Error Resume Next

[NimishK]

Hello

I am searching string which is typed in Textbox and returns the Row number an Cell Address of that string.
I've used Special Cells(xlTypeBlanks) because majority of rows in the Range are with 2 or 3 Empty Cells .

Few Rows are NON-EMPTY because of this I get Error 1004 (Blank) Cells Are Not Found

Although Using On Error Resume Next it works Perfectly.

Will there be any possibility Without using On Error Resume Next we can avoid Error 1004

Private Sub UserForm_Initialize()
    TextBox1.Text = "12345678"
End Sub

Private Sub CmdFindWhichRow_Click()

Dim getRowNo As Range, BlankCells As Range, BlankCellsOfGetRowNo As Range

Set getRowNo = Worksheets("Sheet5").Range("E:E").Find(TextBox1.Text, LookIn:=xlValues, LookAt:=xlWhole)

Set BlankCellsOfGetRowNo = Worksheets("Sheet5").Range("A" & getRowNo.Row & ":J" & getRowNo.Row).SpecialCells(xlCellTypeBlanks)
'The above Gives Run Time Error 1004 as The Row Was full with NON/BLANK (EMPTY) Cells
End Sub

Your valuable inputs shall be appreciated

Thanks
NimishK

 

[RoryA]

You could use Application.Countblank to test the range first.

 

[NimishK]

You could use Application.Countblank to test the range first.

Thank you Sir for the Hint

i tried

using following Syntax

Set BlankCellsOfGetRowNo = Worksheets("Sheet5").Range("A" & getRowNo.Row & ":J" & getRowNo.Row).SpecialCells(xlCellTypeBlanks)

Worksheets("Sheet5").Range("A" & getRowNo.Row & ":J" & getRowNo.Row) = Application.WorksheetFunction.CountBlank(Worksheets("Sheet5").Range("A" & getRowNo.Row & ":J" & getRowNo.Row))

Still I get Error 1004 on line with the wrong syntax i incorporated in.

Will Appreciate if you could help me to correct the syntax

Thanks
NimishK

 

[NimishK]

Wait Sir
Tried the Following

If Application.WorksheetFunction.CountBlank(Worksheets("Sheet1").Range("A" & getRowNo.Row & ":J" & getRowNo.Row)) = "0" Then
MsgBox "There Are Non Empty Cells"
Else
MsgBox "Blank Cells Found"
End If

So therefore still do i have to use the following and to get rid of below

Set BlankCellsOfGetRowNo = Worksheets("Sheet5").Range("A" & getRowNo.Row & ":J" & getRowNo.Row).SpecialCells(xlCellTypeBlanks

NimishK

 

[RoryA]

You would only set the range variable if the countblank is not 0.

 

[NimishK]

You would only set the range variable if the countblank is not 0.

Thank you Sir, for your valuable inputs.

This hint and explanation is sufficient enough

Thank you once more

NimishK