Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

Hi,

The following macro will find the value that you enter in a specific cell. I
have used cell F1 in the code but you can change that to any other cell not
in use but you will need to edit the macro accordingly. Each time you enter a
value in the cell, the row with the value will be selected (Highlighted)
which should make it easy to read the data.

After you paste the macro into your workbook, you will see some lines in
green. These are comment (information) lines and do not form part of the
code. You may need to do some simple editing of the code as per these
instructions.

To copy the macro into your workbook:-

Ensure that you have macros enabled with notification. (See options)

Right click on the worksheet name tab where you have your data. Then Select
View code which will open the VBA editor in the worksheet area.

Copy the macro below and then paste it into the large white area of the VBA
editor.

Alt/F11 will toggle between the VBA editor and the worksheet.

Edit the code if required so that the column with your codes is correct and
the cell where you want to enter the data is correct.

to close the VBA editor, Click on the X with the red background top right of
screen.

Type a code into the cell where you want to enter the data. If not found,
you will get a message accordingly otherwise the row will be highlighted.

As a tip: If you freeze the panes below the column headers and use a cell
within the frozen panes area for the value to find, you will never have to
scroll back to the row to enter a new value.

Feel free to get back to me if you have any problems or want some
alterations to the macro.


--
Regards,

OssieMac


"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

Hi OssieMac,

Thanks so much for your reply, I very much appreciate it.

I've had a bit of a play, and can see from your instructions what I would
need to do, however, I couldn't find your macro?

"OssieMac" wrote:

Hi,

The following macro will find the value that you enter in a specific cell. I
have used cell F1 in the code but you can change that to any other cell not
in use but you will need to edit the macro accordingly. Each time you enter a
value in the cell, the row with the value will be selected (Highlighted)
which should make it easy to read the data.

After you paste the macro into your workbook, you will see some lines in
green. These are comment (information) lines and do not form part of the
code. You may need to do some simple editing of the code as per these
instructions.

To copy the macro into your workbook:-

Ensure that you have macros enabled with notification. (See options)

Right click on the worksheet name tab where you have your data. Then Select
View code which will open the VBA editor in the worksheet area.

Copy the macro below and then paste it into the large white area of the VBA
editor.

Alt/F11 will toggle between the VBA editor and the worksheet.

Edit the code if required so that the column with your codes is correct and
the cell where you want to enter the data is correct.

to close the VBA editor, Click on the X with the red background top right of
screen.

Type a code into the cell where you want to enter the data. If not found,
you will get a message accordingly otherwise the row will be highlighted.

As a tip: If you freeze the panes below the column headers and use a cell
within the frozen panes area for the value to find, you will never have to
scroll back to the row to enter a new value.

Feel free to get back to me if you have any problems or want some
alterations to the macro.


--
Regards,

OssieMac


"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

I can't see OssieMac's code either but unless I am reading you wrong you
don't need VBA.

In B2 enter:

=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

and drag across to E2 using the fill handle.

If your data is wider than Column E then expand that range in the formula.

Now hen you enter a Product Code in A2 the other cells will automatucall
fill in with the other data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB" wrote in message
...
Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 =
Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in
say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

Thank you Sandy Mann!!! It works a treat!

"Sandy Mann" wrote:

I can't see OssieMac's code either but unless I am reading you wrong you
don't need VBA.

In B2 enter:

=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

and drag across to E2 using the fill handle.

If your data is wider than Column E then expand that range in the formula.

Now hen you enter a Product Code in A2 the other cells will automatucall
fill in with the other data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB" wrote in message
...
Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 =
Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in
say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

You are very welcome, thanks for the feed back

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB" wrote in message
...
Thank you Sandy Mann!!! It works a treat!

"Sandy Mann" wrote:

I can't see OssieMac's code either but unless I am reading you wrong you
don't need VBA.

In B2 enter:

=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

and drag across to E2 using the fill handle.

If your data is wider than Column E then expand that range in the
formula.

Now hen you enter a Product Code in A2 the other cells will automatucall
fill in with the other data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB" wrote in message
...
Hello

I have a spreadsheet of a rather large stocklist. I use this to check
off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 =
Profit
margin, etc.

What I would like to do to make this job of checking the prices off
more
efficient, is to type in the product code of the item i am checking in
say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

My apologies for not including the macro. Must have been a 'Seniors Moment'.
It looks like you have your answer now and it is probably a better answer but
as you said that you did have a play with it, I thought that you might like
to see the macro anyway so here it is:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim strToFind As String
Dim foundcell As Range
Dim rngToMatch As Range

'Edit F1 to match the cell where you
'want to enter the code to find
Set rngToMatch = Range("F1")

If Target.Address = rngToMatch.Address Then
With ActiveSheet
'Edit "A:A" to match your column
'to search for product code
Set rngToSearch = Columns("A:A")
End With

strToFind = rngToMatch.Value
Set foundcell = rngToSearch.Find(What:=strToFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundcell Is Nothing Then
foundcell.EntireRow.Select
Else
MsgBox strToFind & " not found"
End If

End If
End Sub

--
Regards,

OssieMac


"BB" wrote:

Hi OssieMac,

Thanks so much for your reply, I very much appreciate it.

I've had a bit of a play, and can see from your instructions what I would
need to do, however, I couldn't find your macro?

"OssieMac" wrote:

Hi,

The following macro will find the value that you enter in a specific cell. I
have used cell F1 in the code but you can change that to any other cell not
in use but you will need to edit the macro accordingly. Each time you enter a
value in the cell, the row with the value will be selected (Highlighted)
which should make it easy to read the data.

After you paste the macro into your workbook, you will see some lines in
green. These are comment (information) lines and do not form part of the
code. You may need to do some simple editing of the code as per these
instructions.

To copy the macro into your workbook:-

Ensure that you have macros enabled with notification. (See options)

Right click on the worksheet name tab where you have your data. Then Select
View code which will open the VBA editor in the worksheet area.

Copy the macro below and then paste it into the large white area of the VBA
editor.

Alt/F11 will toggle between the VBA editor and the worksheet.

Edit the code if required so that the column with your codes is correct and
the cell where you want to enter the data is correct.

to close the VBA editor, Click on the X with the red background top right of
screen.

Type a code into the cell where you want to enter the data. If not found,
you will get a message accordingly otherwise the row will be highlighted.

As a tip: If you freeze the panes below the column headers and use a cell
within the frozen panes area for the value to find, you will never have to
scroll back to the row to enter a new value.

Feel free to get back to me if you have any problems or want some
alterations to the macro.


--
Regards,

OssieMac


"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you

Thank you OssieMac! I've been checking back in the hopes that you may have
responded, as you are right, I would like to see how the macro would work.
I appreciate your help. Glad you've recovered from your "Seniors Moment"....

"OssieMac" wrote:

My apologies for not including the macro. Must have been a 'Seniors Moment'.
It looks like you have your answer now and it is probably a better answer but
as you said that you did have a play with it, I thought that you might like
to see the macro anyway so here it is:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngToSearch As Range
Dim strToFind As String
Dim foundcell As Range
Dim rngToMatch As Range

'Edit F1 to match the cell where you
'want to enter the code to find
Set rngToMatch = Range("F1")

If Target.Address = rngToMatch.Address Then
With ActiveSheet
'Edit "A:A" to match your column
'to search for product code
Set rngToSearch = Columns("A:A")
End With

strToFind = rngToMatch.Value
Set foundcell = rngToSearch.Find(What:=strToFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundcell Is Nothing Then
foundcell.EntireRow.Select
Else
MsgBox strToFind & " not found"
End If

End If
End Sub

--
Regards,

OssieMac


"BB" wrote:

Hi OssieMac,

Thanks so much for your reply, I very much appreciate it.

I've had a bit of a play, and can see from your instructions what I would
need to do, however, I couldn't find your macro?

"OssieMac" wrote:

Hi,

The following macro will find the value that you enter in a specific cell. I
have used cell F1 in the code but you can change that to any other cell not
in use but you will need to edit the macro accordingly. Each time you enter a
value in the cell, the row with the value will be selected (Highlighted)
which should make it easy to read the data.

After you paste the macro into your workbook, you will see some lines in
green. These are comment (information) lines and do not form part of the
code. You may need to do some simple editing of the code as per these
instructions.

To copy the macro into your workbook:-

Ensure that you have macros enabled with notification. (See options)

Right click on the worksheet name tab where you have your data. Then Select
View code which will open the VBA editor in the worksheet area.

Copy the macro below and then paste it into the large white area of the VBA
editor.

Alt/F11 will toggle between the VBA editor and the worksheet.

Edit the code if required so that the column with your codes is correct and
the cell where you want to enter the data is correct.

to close the VBA editor, Click on the X with the red background top right of
screen.

Type a code into the cell where you want to enter the data. If not found,
you will get a message accordingly otherwise the row will be highlighted.

As a tip: If you freeze the panes below the column headers and use a cell
within the frozen panes area for the value to find, you will never have to
scroll back to the row to enter a new value.

Feel free to get back to me if you have any problems or want some
alterations to the macro.


--
Regards,

OssieMac


"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

What I would like to do to make this job of checking the prices off more
efficient, is to type in the product code of the item i am checking in say,
A2, and have the corresponding information appear. Is this possible???

Any help would be greatly appreciated. Thank you