lookup value then clear some cells
 

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

lookup value then clear some cells
 

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

lookup value then clear some cells
 

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 wrote in message
...
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

lookup value then clear some cells
 

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

lookup value then clear some cells
 

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 wrote in message
...
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

lookup value then clear some cells
 

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

lookup value then clear some cells
 

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