I have been searching through google groups to try and find a previous post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but when I
modify it for a cell value it fails.

greg

How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"gregork" wrote in message
...
I have been searching through google groups to try and find a previous post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"gregork" wrote in message
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

"gregork" wrote...
Thanks for the reply Ken. I have tried your code and I'm getting
a compile error: named argument not found........for " SearchFormat:="
....

So you're using Excel 2000 or previous?

Delete the SearchFormat:=... and ReplaceFormat:=... parameters.

Serves me right for not trying it in 2K - Cheers Harlan :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Harlan Grove" wrote in message
...
"gregork" wrote...
Thanks for the reply Ken. I have tried your code and I'm getting
a compile error: named argument not found........for " SearchFormat:="
...

So you're using Excel 2000 or previous?

Delete the SearchFormat:=... and ReplaceFormat:=... parameters.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"gregork" wrote in message
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get
it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"gregork" wrote in message
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get
it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Thanks for the help guys. Sorry I should have mentioned I was using 2000.
One other thing I should have mentioned is that I would prefer to delete
the entire row for the value found in the range Sheet A:A not just the
cell. A rather important bit of information I missed out in the original
post I know. "It's easier to beg forgiveness than ask permission :-)"

I guess I have to throw in a EntireRow.Delete somewhere right?

Regards
Greg


"Ken Wright" wrote in message
...
<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a
compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").Range("A1").Value
repwith = ""

If findme = "" Then Exit Sub

cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If

Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


--------------------------------------------------------------------------
--



"gregork" wrote in message
...
I have been searching through google groups to try and find a
previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A
that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't
get
it
working. I have a code that works for looking up a combobox value
but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004