VBA Code help

C

creese

I have a inventory spreadsheet that I load serial numbers on and o
sheet 1 I have look up tool. I need some help so I can create seperat
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 hav
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 Su
 
O

Otto Moehrbach

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
 
C

creese

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.
 
O

Otto Moehrbach

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
 
C

creese

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.:)
 
C

creese

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.:)
 
O

Otto Moehrbach

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
 
O

Otto Moehrbach

Creese
I found some time so here it is. I assumed that the sheet into which
you enter the numbers is named "Main". Change that as you wish. As
written, the "Main" sheet must be the active sheet when you click on the
button or however you choose to execute the macro.
The macro will search for the value that is in B2 of the "Main" sheet.
It will search Column C of every sheet in the file except the "Main" sheet.
If you need to exclude other sheets as well, the code must be changed to
reflect that. If it is found on any sheet, the macro will put the value
that is in B3 into the cell that is one cell to the right of the found cell.
The macro will then display an "Updated Successfully" message box.
If the B2 value is not found on any sheet, the macro will display a
message box saying "The IMEI number given could not be found."
You may have some problems with line wrapping of the macro code in this
message, in which case you will get an error. If you have this problem, you
can send me your email address and I'll send you the small file I used for
this. My email address is [email protected]. Remove the "nop" from
this address. HTH Otto
Sub CopyToShts()
Dim ws As Worksheet
Dim RngColC As Range
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Main" Then GoTo NextSht
With ws
Set RngColC = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
If Not RngColC.Find(What:=[B2].Value, LookAt:=xlWhole) Is
Nothing Then
RngColC.Find(What:=[B2].Value, LookAt:=xlWhole).Offset(, 1)
= [B3].Value
MsgBox "Updated Successfully"
Exit Sub
End If
End With
NextSht:
Next ws
MsgBox "The IMEI number given could not be found.", 16, "IMEI Not Found"
End Sub
 
Top