New user needs help
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 |
New user needs help
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 |
New user needs help
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 |
New user needs help
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 |
New user needs help
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 |
New user needs help
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 |
New user needs help
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 |
All times are GMT +1. The time now is 03:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com