Another code for deleting row

[gregork]

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

 

[MSP77079]

Try this:

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

 

[Dave Peterson]

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")?

 

[Bob Phillips]

Gregor,

I tried it and it works for me.

It did however occur to me that it might be formatting, the value on 'Blends
Sheet' might be formatted, 'Blends Produced' is not, and as you are using
the Text property, you get no match.

Try
lookup_value = Sheets("Blend Sheet").Range("O3").Value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)