VBA inputbox - vlookup and edit

stn1100

New Member
Joined
Aug 31, 2018
Messages
6
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
See if this VBA does what you want:
Code:
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top