How to manipulate a row with a certain value

[Bill]

I am trying to look for a variable value in a range, select part of the row
that value is in, and clear that selection.

Seems straightforward and I cannot for the life of me get it.

Help!

Thanks

[JMB]

Can't write anything specific to your situation with no details, however, as
an example, this macro would find the cell (assuming there is only one cell)
in the range B11:C16, then clear the cell to the left through the fifth cell
from the right. So if B14 contained 5, then A14:G14 would be cleared. The
find method has additional arguments (you can find them in VBA help file).
If you can have multiple matches you may be able to set up a loop, or use
FindNext (just ideas - you'll need to play with it to see what works best for
you).


Sub test()
Const x As Long = 5
Dim Rng As Range

Set Rng = Range("B11:C16").Find(what:=x)
With Rng
Range(.Offset(0, -1), .Offset(0, 5)).Clear
End With

End Sub


"Bill" wrote:

> I am trying to look for a variable value in a range, select part of the row
> that value is in, and clear that selection.
>
> Seems straightforward and I cannot for the life of me get it.
>
> Help!
>
> Thanks

[Bill]

Thanks for the quick response.

I am still not getting it.

Here is my situation:

1. I have a database of values all keyed off of a unique ID number in the
left-most column.
2. I want to be able to clear the contents of the first 8 columns of a row
that is identified by matching a user-input ID number.

When I run the code you provided, my range variable "rng" is being set = to
the ID number. Then the:

Range(.Offset(0, 0), .Offset(0, 8)).ClearContents

line gives me a "method of 'range' object '_global' failed" error

Maybe it is just too late at night to be doing this stuff?

Help?

"JMB" wrote:

> Can't write anything specific to your situation with no details, however, as
> an example, this macro would find the cell (assuming there is only one cell)
> in the range B11:C16, then clear the cell to the left through the fifth cell
> from the right. So if B14 contained 5, then A14:G14 would be cleared. The
> find method has additional arguments (you can find them in VBA help file).
> If you can have multiple matches you may be able to set up a loop, or use
> FindNext (just ideas - you'll need to play with it to see what works best for
> you).
>
>
> Sub test()
> Const x As Long = 5
> Dim Rng As Range
>
> Set Rng = Range("B11:C16").Find(what:=x)
> With Rng
> Range(.Offset(0, -1), .Offset(0, 5)).Clear
> End With
>
> End Sub
>
>
> "Bill" wrote:
>
> > I am trying to look for a variable value in a range, select part of the row
> > that value is in, and clear that selection.
> >
> > Seems straightforward and I cannot for the life of me get it.
> >
> > Help!
> >
> > Thanks

[JMB]

In the last post, the criteria was represented by x. I think I got ahead of
myself and left that out.

Assuming the leftmost column of your table is column A, then something like
the following will prompt for the ID, search Column A for a match, then clear
the contents of column A through Column H of the row containing your match.

Rng is an object variable that represents the cell matching your criteria.
Range(Rng, Rng.Offset(0, 7) is a range object that is defined by a beginning
and ending cell. The first is Rng (the cell matching the criteria) and the
second is the cell 7 columns to the right - accomplished by using
Rng.Offset(0,7)

If your leftmost column is not column A, then you will need to change
Range("A:A") to whatever you need.

Is this in the right direction?

Sub test()
Dim Criteria As Variant
Dim Rng As Range

Criteria = InputBox("Input ID Number")

If Criteria <> "" Then
Set Rng = Range("A:A").Find(what:=Criteria)
If Not Rng Is Nothing Then
Range(Rng, Rng.Offset(0, 7)).Clear
Else: MsgBox "ID Not Found"
End If
End If

End Sub



"Bill" wrote:

> Thanks for the quick response.
>
> I am still not getting it.
>
> Here is my situation:
>
> 1. I have a database of values all keyed off of a unique ID number in the
> left-most column.
> 2. I want to be able to clear the contents of the first 8 columns of a row
> that is identified by matching a user-input ID number.
>
> When I run the code you provided, my range variable "rng" is being set = to
> the ID number. Then the:
>
> Range(.Offset(0, 0), .Offset(0, 8)).ClearContents
>
> line gives me a "method of 'range' object '_global' failed" error
>
> Maybe it is just too late at night to be doing this stuff?
>
> Help?
>
> "JMB" wrote:
>
> > Can't write anything specific to your situation with no details, however, as
> > an example, this macro would find the cell (assuming there is only one cell)
> > in the range B11:C16, then clear the cell to the left through the fifth cell
> > from the right. So if B14 contained 5, then A14:G14 would be cleared. The
> > find method has additional arguments (you can find them in VBA help file).
> > If you can have multiple matches you may be able to set up a loop, or use
> > FindNext (just ideas - you'll need to play with it to see what works best for
> > you).
> >
> >
> > Sub test()
> > Const x As Long = 5
> > Dim Rng As Range
> >
> > Set Rng = Range("B11:C16").Find(what:=x)
> > With Rng
> > Range(.Offset(0, -1), .Offset(0, 5)).Clear
> > End With
> >
> > End Sub
> >
> >
> > "Bill" wrote:
> >
> > > I am trying to look for a variable value in a range, select part of the row
> > > that value is in, and clear that selection.
> > >
> > > Seems straightforward and I cannot for the life of me get it.
> > >
> > > Help!
> > >
> > > Thanks

[Bill]

Beautiful!

Thank you for your help.
Maybe I should buy a book and learn this for real.

-Bill

"JMB" wrote:

> In the last post, the criteria was represented by x. I think I got ahead of
> myself and left that out.
>
> Assuming the leftmost column of your table is column A, then something like
> the following will prompt for the ID, search Column A for a match, then clear
> the contents of column A through Column H of the row containing your match.
>
> Rng is an object variable that represents the cell matching your criteria.
> Range(Rng, Rng.Offset(0, 7) is a range object that is defined by a beginning
> and ending cell. The first is Rng (the cell matching the criteria) and the
> second is the cell 7 columns to the right - accomplished by using
> Rng.Offset(0,7)
>
> If your leftmost column is not column A, then you will need to change
> Range("A:A") to whatever you need.
>
> Is this in the right direction?
>
> Sub test()
> Dim Criteria As Variant
> Dim Rng As Range
>
> Criteria = InputBox("Input ID Number")
>
> If Criteria <> "" Then
> Set Rng = Range("A:A").Find(what:=Criteria)
> If Not Rng Is Nothing Then
> Range(Rng, Rng.Offset(0, 7)).Clear
> Else: MsgBox "ID Not Found"
> End If
> End If
>
> End Sub
>
>
>
> "Bill" wrote:
>
> > Thanks for the quick response.
> >
> > I am still not getting it.
> >
> > Here is my situation:
> >
> > 1. I have a database of values all keyed off of a unique ID number in the
> > left-most column.
> > 2. I want to be able to clear the contents of the first 8 columns of a row
> > that is identified by matching a user-input ID number.
> >
> > When I run the code you provided, my range variable "rng" is being set = to
> > the ID number. Then the:
> >
> > Range(.Offset(0, 0), .Offset(0, 8)).ClearContents
> >
> > line gives me a "method of 'range' object '_global' failed" error
> >
> > Maybe it is just too late at night to be doing this stuff?
> >
> > Help?
> >
> > "JMB" wrote:
> >
> > > Can't write anything specific to your situation with no details, however, as
> > > an example, this macro would find the cell (assuming there is only one cell)
> > > in the range B11:C16, then clear the cell to the left through the fifth cell
> > > from the right. So if B14 contained 5, then A14:G14 would be cleared. The
> > > find method has additional arguments (you can find them in VBA help file).
> > > If you can have multiple matches you may be able to set up a loop, or use
> > > FindNext (just ideas - you'll need to play with it to see what works best for
> > > you).
> > >
> > >
> > > Sub test()
> > > Const x As Long = 5
> > > Dim Rng As Range
> > >
> > > Set Rng = Range("B11:C16").Find(what:=x)
> > > With Rng
> > > Range(.Offset(0, -1), .Offset(0, 5)).Clear
> > > End With
> > >
> > > End Sub
> > >
> > >
> > > "Bill" wrote:
> > >
> > > > I am trying to look for a variable value in a range, select part of the row
> > > > that value is in, and clear that selection.
> > > >
> > > > Seems straightforward and I cannot for the life of me get it.
> > > >
> > > > Help!
> > > >
> > > > Thanks