Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
I have a inventory spreadsheet that I load serial numbers on and on sheet 1 I have look up tool. I need some help so I can create seperate pages for each item, and have my lookup tool search through each page. Currently it only looks up on the one page. Ok Here is my code I have now: Sub UpdateReceipt() SerialFound = "NotYet" myError = "" Range("b1").Clear mySerial = Range("b2").Value myReceipt = Range("b3").Value Sheets("Sheet2").Select Range("c1").Select Do While Selection.Value < "" Selection.Offset(1, 0).Select If Selection.Value = mySerial Then If IsEmpty(Selection.Offset(0, 1).Value) Then Selection.Offset(0, 1).Value = myReceipt SerialFound = "Yes" myError = "Updated Successfully" Else myError = "Error, Receipt Exists for that Serial #" SerialFound = "No" End If End If Loop If SerialFound = "NotYet" Then myError = "Serial Number not found" Sheets("Sheet1").Select Range("b1").Value = myError If SerialFound = True Then Range("b3").Clear End If End Sub -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
What is "look up tool"? Is it the macro you posted? It is not clear what
you are asking for from what you posted. Please post back and provide a step-by-step procedure of what you want this macro to do, as written. Then provide another step-by-step procedure of what you want this macro to do after it is changed. HTH Otto "creese" wrote in message ... I have a inventory spreadsheet that I load serial numbers on and on sheet 1 I have look up tool. I need some help so I can create seperate pages for each item, and have my lookup tool search through each page. Currently it only looks up on the one page. Ok Here is my code I have now: Sub UpdateReceipt() SerialFound = "NotYet" myError = "" Range("b1").Clear mySerial = Range("b2").Value myReceipt = Range("b3").Value Sheets("Sheet2").Select Range("c1").Select Do While Selection.Value < "" Selection.Offset(1, 0).Select If Selection.Value = mySerial Then If IsEmpty(Selection.Offset(0, 1).Value) Then Selection.Offset(0, 1).Value = myReceipt SerialFound = "Yes" myError = "Updated Successfully" Else myError = "Error, Receipt Exists for that Serial #" SerialFound = "No" End If End If Loop If SerialFound = "NotYet" Then myError = "Serial Number not found" Sheets("Sheet1").Select Range("b1").Value = myError If SerialFound = True Then Range("b3").Clear End If End Sub -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
The look up tool is just a button I have created to process the macro. I just can't get it to look through multiple sheets. Currently on sheet one I have a chart that looks like this: Updated Successfully IMEI # 351609012359835 "Click to Update" (Button) Receipt # 611049 On sheet two I have all of the serial numbers listed and further details: Brand Model IMEI Receipt # Ericsson T62U 10124716931015 Ericsson T237 10273003777258 611087 Ericsson T237 10273003777290 611095 Ericsson W6001 357446000798235 Ericsson Z520A 351609011849133 611029 Ericsson Z520A 351609011846626 Ericsson Z520A 351609011147819 611089 Ericsson Z520A 351609012359835 611049 I want to be able to put the receipt number in that goes with the serial number even if the serial number is on a different sheet. -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
Creese
Let me see if I have this right. You have a sheet you call sheet One. On that sheet you have the following, as well as a button to trigger the macro: Updated Successfully IMEI # 351609012359835 Receipt # 611049 I have some questions about this: About the "Updated Successfully". Looking at your macro I see that this text is placed in a cell if and when the data has been successfully updated. Now let me ask you why you are doing this. Are you doing this just to advise the user that the update has been completed successfully? If so, would you like to have a simple message box to pop up on the screen saying so instead? Or perhaps you want that text in that cell for some reason? The IMEI line: Is all that in one cell? Or is "IMEI #" in one cell and the serial number in the adjacent cell? The same question for the Receipt line. You say that you have sheet two with the Brand, Model, and IMEI number. I take it that each of these is in a separate column. Is that correct? Then you have a 4th column with "Receipt #" as the header. Is that correct? You mention multiple sheets and that you need to search these other sheets, not just sheet two. Is it correct that you have, in these other sheets, essentially what you have in sheet two but with different IMEI numbers? Is it also correct that you want the macro to search each of these other sheets until it finds that IMEI number, and then place the Receipt number one cell to the right of that found IMEI number? Otto "creese" wrote in message ... The look up tool is just a button I have created to process the macro. I just can't get it to look through multiple sheets. Currently on sheet one I have a chart that looks like this: Updated Successfully IMEI # 351609012359835 "Click to Update" (Button) Receipt # 611049 On sheet two I have all of the serial numbers listed and further details: Brand Model IMEI Receipt # Ericsson T62U 10124716931015 Ericsson T237 10273003777258 611087 Ericsson T237 10273003777290 611095 Ericsson W6001 357446000798235 Ericsson Z520A 351609011849133 611029 Ericsson Z520A 351609011846626 Ericsson Z520A 351609011147819 611089 Ericsson Z520A 351609012359835 611049 I want to be able to put the receipt number in that goes with the serial number even if the serial number is on a different sheet. -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
Ok reguarding the update successfully, I did just add that so the person using it would know it worked. A message box on the screen would be great. IMEI # is in one box (A2) and the serial number is in the adjacent cell (B2) (IMEI is our word for serial #) Same goes for receipt (A3) and number is entered in B3. Everything on sheet two is seperate columns In the 4th column is the receipt column. This is where I want the info from sheet 1 to place the receipt number typed in on sheet 1. I do want to have multiple sheets like sheet 2 and have my macro search all the sheets to find the correct IMEI # and put the receipt # beside of it. Thanks for your help.:) -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
Ok reguarding the update successfully, I did just add that so the person using it would know it worked. A message box on the screen would be great. IMEI # is in one box (A2) and the serial number is in the adjacent cell (B2) (IMEI is our word for serial #) Same goes for receipt (A3) and number is entered in B3. Everything on sheet two is seperate columns In the 4th column is the receipt column. This is where I want the info from sheet 1 to place the receipt number typed in on sheet 1. I do want to have multiple sheets like sheet 2 and have my macro search all the sheets to find the correct IMEI # and put the receipt # beside of it. Thanks for your help.:) -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Code help
Creese
I'm somewhat tied up for this weekend and will get on this Monday. If you need it sooner, maybe someone reading all this will step in. Otto "creese" wrote in message ... Ok reguarding the update successfully, I did just add that so the person using it would know it worked. A message box on the screen would be great. IMEI # is in one box (A2) and the serial number is in the adjacent cell (B2) (IMEI is our word for serial #) Same goes for receipt (A3) and number is entered in B3. Everything on sheet two is seperate columns In the 4th column is the receipt column. This is where I want the info from sheet 1 to place the receipt number typed in on sheet 1. I do want to have multiple sheets like sheet 2 and have my macro search all the sheets to find the correct IMEI # and put the receipt # beside of it. Thanks for your help.:) -- creese ------------------------------------------------------------------------ creese's Profile: http://www.excelforum.com/member.php...o&userid=35508 View this thread: http://www.excelforum.com/showthread...hreadid=554590 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
close form code | Excel Discussion (Misc queries) |