Replace lookup value with Textbox value VBA

olorin_117

New Member
Joined
Jan 19, 2022
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
I have a table that the user doesn't see with data in rows A and B. There are data in row A (for example apples, oranges etc) and row B is empty. User picks from a combobox a value that belongs to row A, the value is stored in a cell. After that user must put an integer in a textbox that belongs to row B (which is empty at first). For example first the user picks Apples and after that has to put the number 10 (as in kilos).I am trying to put the value from the textbox to row B.. Until now I have managed to make a vlookup in vba that finds the corresponding empty cell but i cant figure out how to put the value from the textbox to said cell. Fairly new to vba, so any help will be greatly appreciated
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
User picks from a combobox a value that belongs to row A
How to load the data in the combobox?
The combobox is in a form?
Or is it in the sheet, if it is in the sheet is it an ActiveX control?
 
Upvote 0
How to load the data in the combobox?
The combobox is in a form?
Or is it in the sheet, if it is in the sheet is it an ActiveX control?
The combobox is in a userform,populated with data from column A of said range.The value he picks is stored in a cell (hidden) for other reasons.The textbox is also in a second userform,loaded at the end of the day
 
Upvote 0
You must be specific with the data. Put here the macro or code or instruction that you use to load the combo. what is the name of the sheet, what is the name of the combo, what is the name of the textbox. That way it will be easier to help you.
 
Upvote 0
The data from the combobox I mentioned is stored in a cell in Sheet1, cell "V7". The Range with the 2 columns that the user doesnt see is at Sheet3. At the end of the day, user loads a userform with a textbox. The data from the textbox belong to Column B. For example at the start of the day user picks a value from column A and at the end of the day he inputs a number in the textbox that belong to said value. The vlookup is working fine, but I cant figure out a way to replace the cell with a new value

VBA Code:
Private Sub TextBox1_Change()()
Dim frt As String
Dim kilo As Integer

frt = Worksheets("Sheet1").Range("V7").Value
Set myrange = Worksheets("Sheet3").Range("A15:B32")
kilo = Application.WorksheetFunction.VLookup(cofpack, myrange, 2, False)

End Sub
 
Upvote 0
After enough digging I made it work by NOT using .vlookup but .find, and using a command button in the userform

VBA Code:
Private Sub CommandButton2_Click()

Dim myrange As Range
Dim frt As String

frt = Worksheets("Sheet1").Range("V7").Value

Set myrange = Worksheets("Sheet3").Range("A15:A32").Find(What:=frt, Lookat:=xlWhole, MatchCase:=False)

If Not myrange Is Nothing Then
        
        myrange.offset(, 1).Value = TextBox1.Value
    End If

End Sub
 
Upvote 0
Solution
You put everything except how you load the data into the combo. The solution is simpler than a vlookup or find method. for example if you load the combobox like this:

VBA Code:
Combobox1.list = sheets("sheet3").Range("A15:A32").value

Then:
VBA Code:
Private Sub CommandButton2_Click()
sheets("sheet3").Range("B" & combobox1.Listindex + 15).Value = Textbox1.value
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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