lookup value then clear some cells

[Y Sbuty]

Hello,

I am trying to set up a stock control spreadsheet...

What i'd like to be able to do is enter a stock number in a cell on sheet1,
press a 'delete' button, it would then lookup the stock number in a list on
sheet2, and then delete 3 cells to the right of the stock number.

It sounds easy enough, but i'm struggling a bit!

Any help would be greatly appreciated, Thanks,

YS

[Jim Thomlinson]

Does/could the stock number exist more than once on the second sheet meaning
that more than one cell is potentially going to need to be cleared?
--
HTH...

Jim Thomlinson


"Y Sbuty" wrote:

> Hello,
>
> I am trying to set up a stock control spreadsheet...
>
> What i'd like to be able to do is enter a stock number in a cell on sheet1,
> press a 'delete' button, it would then lookup the stock number in a list on
> sheet2, and then delete 3 cells to the right of the stock number.
>
> It sounds easy enough, but i'm struggling a bit!
>
> Any help would be greatly appreciated, Thanks,
>
> YS

[Tom Ogilvy]

Dim rng as Range
Dim res as Variant
With Worksheets(2)
set rng = .range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
res = Application.Match(activeCell,rng,0)
if not iserror(res) then
rng(res,2).Resize(1,3).ClearContents
End if

--
Regards,
Tom Ogilvy


"Y Sbuty" <Y [email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I am trying to set up a stock control spreadsheet...
>
> What i'd like to be able to do is enter a stock number in a cell on
sheet1,
> press a 'delete' button, it would then lookup the stock number in a list
on
> sheet2, and then delete 3 cells to the right of the stock number.
>
> It sounds easy enough, but i'm struggling a bit!
>
> Any help would be greatly appreciated, Thanks,
>
> YS

[Y Sbuty]

The stock number is unique and stored in column B, when the stock numer is
found, i need to clear the 3 cells to the right of the stock number (same
row), but leaving the stock number

"Jim Thomlinson" wrote:

> Does/could the stock number exist more than once on the second sheet meaning
> that more than one cell is potentially going to need to be cleared?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Y Sbuty" wrote:
>
> > Hello,
> >
> > I am trying to set up a stock control spreadsheet...
> >
> > What i'd like to be able to do is enter a stock number in a cell on sheet1,
> > press a 'delete' button, it would then lookup the stock number in a list on
> > sheet2, and then delete 3 cells to the right of the stock number.
> >
> > It sounds easy enough, but i'm struggling a bit!
> >
> > Any help would be greatly appreciated, Thanks,
> >
> > YS

[Y Sbuty]

Thanks Tom, but i'm struggling to integrate your code into my spreadsheet.
(as a beginner) i'm not sure what i need to change to get it to work.

The worksheet where i want to type the stock number is called "Query Page",
stock number to be typed into cell B10

The worksheet with the list is called "Stocklist", with the stock numbers in
the range B3:B502

Cheers,

YS

"Tom Ogilvy" wrote:

> Dim rng as Range
> Dim res as Variant
> With Worksheets(2)
> set rng = .range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
> End with
> res = Application.Match(activeCell,rng,0)
> if not iserror(res) then
> rng(res,2).Resize(1,3).ClearContents
> End if
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Y Sbuty" <Y [email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I am trying to set up a stock control spreadsheet...
> >
> > What i'd like to be able to do is enter a stock number in a cell on
> sheet1,
> > press a 'delete' button, it would then lookup the stock number in a list
> on
> > sheet2, and then delete 3 cells to the right of the stock number.
> >
> > It sounds easy enough, but i'm struggling a bit!
> >
> > Any help would be greatly appreciated, Thanks,
> >
> > YS
>
>
>

[Jim Thomlinson]

Sub DeleteStock()
Dim wksMain As Worksheet
Dim wksToSearch As Worksheet
Dim rngToFind As Range
Dim rngToSearch As Range
Dim rngFound As Range

Set wksMain = Sheets("Sheet1") 'sheet with the button
Set rngToFind = wksMain.Range("A2") 'Cell to look up
Set wksToSearch = Sheets("Sheet2") 'Sheet to search
Set rngToSearch = wksToSearch.Columns("B") 'Range to search

Set rngFound = rngToSearch.Find(rngToFind.Value)
If rngFound Is Nothing Then
MsgBox "Sorry that part was not found"
Else
rngFound.Offset(0, 3).ClearContents
End If

End Sub

--
HTH...

Jim Thomlinson


"Y Sbuty" wrote:

> The stock number is unique and stored in column B, when the stock numer is
> found, i need to clear the 3 cells to the right of the stock number (same
> row), but leaving the stock number
>
> "Jim Thomlinson" wrote:
>
> > Does/could the stock number exist more than once on the second sheet meaning
> > that more than one cell is potentially going to need to be cleared?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Y Sbuty" wrote:
> >
> > > Hello,
> > >
> > > I am trying to set up a stock control spreadsheet...
> > >
> > > What i'd like to be able to do is enter a stock number in a cell on sheet1,
> > > press a 'delete' button, it would then lookup the stock number in a list on
> > > sheet2, and then delete 3 cells to the right of the stock number.
> > >
> > > It sounds easy enough, but i'm struggling a bit!
> > >
> > > Any help would be greatly appreciated, Thanks,
> > >
> > > YS

[Y Sbuty]

Thanks, that works perfectly

"Jim Thomlinson" wrote:

> Sub DeleteStock()
> Dim wksMain As Worksheet
> Dim wksToSearch As Worksheet
> Dim rngToFind As Range
> Dim rngToSearch As Range
> Dim rngFound As Range
>
> Set wksMain = Sheets("Sheet1") 'sheet with the button
> Set rngToFind = wksMain.Range("A2") 'Cell to look up
> Set wksToSearch = Sheets("Sheet2") 'Sheet to search
> Set rngToSearch = wksToSearch.Columns("B") 'Range to search
>
> Set rngFound = rngToSearch.Find(rngToFind.Value)
> If rngFound Is Nothing Then
> MsgBox "Sorry that part was not found"
> Else
> rngFound.Offset(0, 3).ClearContents
> End If
>
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Y Sbuty" wrote:
>
> > The stock number is unique and stored in column B, when the stock numer is
> > found, i need to clear the 3 cells to the right of the stock number (same
> > row), but leaving the stock number
> >
> > "Jim Thomlinson" wrote:
> >
> > > Does/could the stock number exist more than once on the second sheet meaning
> > > that more than one cell is potentially going to need to be cleared?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Y Sbuty" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am trying to set up a stock control spreadsheet...
> > > >
> > > > What i'd like to be able to do is enter a stock number in a cell on sheet1,
> > > > press a 'delete' button, it would then lookup the stock number in a list on
> > > > sheet2, and then delete 3 cells to the right of the stock number.
> > > >
> > > > It sounds easy enough, but i'm struggling a bit!
> > > >
> > > > Any help would be greatly appreciated, Thanks,
> > > >
> > > > YS