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
 

stn1100

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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
 

stn1100

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

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top