VBA inputbox - vlookup and edit

[stn1100]

I need an inputbox to vlookup "part number" and then replace/update its current batch number "batch".

part number batch
0501322391 0529765
0501218232 0539866
4464335010 4699467
4464335009 6204844

 

[Joe4]

Welcome to the Board!

I think we are going to need a bit more details, like where all this data is located and what is being updated where.

Here is a link with some tips on how to post good, complete questions that allow us to help you best.
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[stn1100]

I continually get a web page error and it reboots the page. I can't enter but so much before it kicks me out.
my apologies for trying to keep it short.

 

[Joe4]

I continually get a web page error and it reboots the page.

Are you posting from a PC or a phone?
Which browser are you using? I have the best luck with Google Chrome - really don't have any issues with this.
You may to try refreshing your cache.

Also note, maybe you can type up your complete question in Word, then Copy & Paste it here. That way, you won't lose everything you've type if your browser acts up.

 

[stn1100]

combobox is used on another spreadsheet "scan" that will activate this inputbox that will change data kept in another spreadsheet named "batch"
when "batchform" is entered into this combobox I want the inputbox to open and ask for a part number that will search in spreadsheet "batch" - then once its found the inputbox would ask for the batch number which once entered will over write the current number.

 

[Joe4]

I am looking for specific details:
- What is the address of the range where the data resides?
- Which column will it be matching on and which column will it be updating?

 

[stn1100]

exploder! I may have to use my home computer. work pc is regulated with what we can have access too. I tried to paste from text - it doesn't transfer... I'll try word.

 

[stn1100]

("batch") A:B

column A (part number) column B (batch)

 

[Joe4]

See if this VBA does what you want:

Sub MyReplaceMacro()

    Dim partNum As String
    Dim batch As String
    
    partNum = InputBox("Enter the part number you wish to search for")
    
'   See if part number found in column A on "Batch" sheet
    Sheets("Batch").Activate
    On Error GoTo not_found
    Columns("A:A").Find(What:=partNum, After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    
'   Prompt for new batch number
    batch = InputBox("Enter the new batch number")
    ActiveCell.Offset(0, 1) = batch
        
    Exit Sub


'Error handling if part number not found
not_found:
    MsgBox "Cannot find part number " & partNum & " on Batch sheet", vbOKOnly, "ERROR!"
    
End Sub

 

[stn1100]

excellent work!
I had to define the spreadsheet in the search. other than that its awesome! My Heijunka board is complete!
worksheets("batch").columns ("A:A").find...