Sorry to post on this again but I realised I need to delete another row on a
different sheet using the same command button click event. Here is the code
I have added to the code that deletes rows on another sheet:

Dim anotherrng As Range, anotherres As Variant, anotherrng1 As Range
Dim lookup_value
lookup_value = Sheets("Blend Sheet").Range("O3").Text
Set rng = Worksheets("Blends Produced").Range("I2:I6000")

res = Application.Match(lookup_value, rng, 0)

If Not IsError(res) Then
Set rng1 = rng(res)
rng1.EntireRow.Delete
MsgBox "Component Deleted"
Else
MsgBox "No Match "
End If

Unload Me

End Sub

The code is supposed to lookup the number in 'Blend Sheet'! O3 and find it
in 'Blends Produced'! I2:I6000 and then delete the entire row. But it just
don't work.....I keep getting "No Match ". Could some kind soul please
show me where I have stuffed up.

gregorK

Try this:

res = Application.WorksheetFunction.Match(lookup_value, rng, 0

--
Message posted from http://www.ExcelForum.com

Is there a real reason you used .Text in:
lookup_value = Sheets("Blend Sheet").Range("O3").Text

You might be changing something that should be .value.

What kind of data is in Range("o3")?

gregork wrote:

Sorry to post on this again but I realised I need to delete another row on a
different sheet using the same command button click event. Here is the code
I have added to the code that deletes rows on another sheet:

Dim anotherrng As Range, anotherres As Variant, anotherrng1 As Range
Dim lookup_value
lookup_value = Sheets("Blend Sheet").Range("O3").Text
Set rng = Worksheets("Blends Produced").Range("I2:I6000")

res = Application.Match(lookup_value, rng, 0)

If Not IsError(res) Then
Set rng1 = rng(res)
rng1.EntireRow.Delete
MsgBox "Component Deleted"
Else
MsgBox "No Match "
End If

Unload Me

End Sub

The code is supposed to lookup the number in 'Blend Sheet'! O3 and find it
in 'Blends Produced'! I2:I6000 and then delete the entire row. But it just
don't work.....I keep getting "No Match ". Could some kind soul please
show me where I have stuffed up.

gregorK

--

Dave Peterson