On error equals blank

[victorel21]

Hi, I am making an organizer file for cycle count selection in the company I work for, right now im stuck in this piece of code I want that if the cells find formula below gives error because it was not found that the textbox results in blank.

Private Sub ComboBox1_Change()

Dim x

x = Form4.ComboBox1

Sheets("Classification").Activate

With Sheets("Classification")

On Error Resume Next
Form4.TextBox2 = Cells(.Range("C:C").Find(x, Lookat:=xlWhole).row, 4)

End With

With WorksheetFunction

Form4.TextBox3 = Format(.CountIfs(Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox4 = Format(.SumIfs(Sheets("Classification").Range("G:G"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox5 = Format(.SumIfs(Sheets("Classification").Range("H:H"), Sheets("Classification").Range("C:C"), x), "#,##0.00")

End With

End Sub

I have tried various error handling methods but none seem to work, the code should run normally with the exception textbox should be = to blank. Any help is appreciated. Thanks.

 

[Fluff]

How about

Private Sub ComboBox1_Change()

Dim x
Dim Fnd As Range
x = Form4.ComboBox1

Sheets("Classification").Activate

With Sheets("Classification")

Set Fnd = .Range("C:C").Find(x, Lookat:=xlWhole)

End With
If Not Fnd Is Nothing Then
   Form4.TextBox2 = Fnd.Offset(, 4).Value
End If
With WorksheetFunction

Form4.TextBox3 = Format(.CountIfs(Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox4 = Format(.SumIfs(Sheets("Classification").Range("G:G"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox5 = Format(.SumIfs(Sheets("Classification").Range("H:H"), Sheets("Classification").Range("C:C"), x), "#,##0.00")

End With

End Sub

 

[victorel21]

How about

Private Sub ComboBox1_Change()

Dim x
Dim Fnd As Range
x = Form4.ComboBox1

Sheets("Classification").Activate

With Sheets("Classification")

Set Fnd = .Range("C:C").Find(x, Lookat:=xlWhole)

End With
If Not Fnd Is Nothing Then
   Form4.TextBox2 = Fnd.Offset(, 4).Value
End If
With WorksheetFunction

Form4.TextBox3 = Format(.CountIfs(Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox4 = Format(.SumIfs(Sheets("Classification").Range("G:G"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
Form4.TextBox5 = Format(.SumIfs(Sheets("Classification").Range("H:H"), Sheets("Classification").Range("C:C"), x), "#,##0.00")

End With

End Sub

This worked great! Would have never come up with this without your help thanks!

 

[Fluff]

You're welcome & thanks for the feedback.