Vlookup in a Form 1

[SebbeKine]

Hello,

I want to make a form with 2 textboxes and 2 commandbuttons.
The first textbox has to refer to te cellname Naam on the first sheet of the workbook. In the second textbox there wil be the result (with vlookup in the second row of the sheet) of the first textbox. The firts commandbutton is to close the form with no changes. The second commandbutton has to apply the change made in the second textbox. It has to search for the correct cell in the sheet.
In the sheet I have 2 coloms. In the firts there are names and in the second there are numbers.
I've made the form, but I don't know how to make the code.

I'm new in VBA.

Thanks

 

[PHV]

Again, have a look at the Match worksheet function ...

 

[SebbeKine]

I ve already looked it up, but I really don't know how to do it. I'm looking to it for about 4 days now, so I think I put enough effort in it.

Is it this that you mean?
= CELL("address",INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-15)),ABS(A1:A5-15),0)))
But I don't know how to use it in my project.

 

[SkipVought]

"...search the exact row..."

Based on WHAT? A match value? Last row with data?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

So now I have this for commandbutton2:
Private Sub Aanpassen_click()
lRow = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("$A:$B"), 2, False)
Sheets("Uurlonen").Cells(lRow, "B").Value = TextBox1.Text
Unload frmUurlonen
End Sub

But there is nothing changing in the sheet.

 

[SebbeKine]

example:
Row Colum A ColumB
1 Sebastien 14
2 Bart 14
3 Alex 16
4 Tom 20
5 Tim 16
6 Tia 20

I open the form, and select in Combobox1 the client Tom.
In Textbox1 I get (with vlookup) the value 20.
But I want to change that value to 21, so I type in Textbox1 the value 21.
Now when I press commandbutton2, I want that the value in the sheet change to the value I put in Textbox1.

In this example I only have 6 names, so it's easy to see wich row. But in my sheet I have 70 names, it's much harder. Thats why I use VBA.

 

[SebbeKine]

This isn't working also:
Private Sub Aanpassen_click()
Target = Application.VLookup(ComboBox1.Text, _
Worksheets("Uurlonen").Range("$A:$B"), 2, False)
Target = TextBox1.Value
Unload frmUurlonen
End Sub

 

[SkipVought]

Is there a reason that you're doing this in a FORM???

Using the AutoFilter directly on a sheet would be much easier and sans code!!!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

It is because I do this for a friend. He doesn't know anything about excel.
So I've made some userforms for him so that he only have to use these.
Everything went OK, only for this one.

 

[SkipVought]

So you ...

1. make it a table
[tt]
Name Amount
Sebastien 14
Bart 14
Alex 16
Tom 20
Tim 16
Tia 20

[/tt]
2. You turn on the AutoFilter

3. You tell him, "Select the name you want using that little drop down arrow, just like the ComboBox in the form I was going to do for you but is much more complicated and stuff."

4. "Then change the Amount if you like, or not if you don't like."

VOLA!!!

PS: You can change Amount to anything you like, or not if you don't like.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

PPS:

Homework can be [red]HELL[/red], but all that blood sweat and tears will eventually pay off.


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

Thanks.
I will look further.
Let me know if you find anything.

 

[SebbeKine]

I was tinking that this should be working, but it isn't:

Private Sub Aanpassen_click()
Dim x As Variant
x = Application.Match(Naam, Uurlonen.Range("A:A"), 0)
Sheets("Uurlonen").Cells(x, "B").Value = TextBox1.Value
Unload frmUurlonen
End Sub

It gives me an error 424.

x gives me the correct row

Thanks to PHV!!

 

[SkipVought]

"It gives me an error 424."

On what statement?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SebbeKine]

It's OK, the code is working now.

Thanks to PHV!! It wasn't so hard to find or do I have to say MATCH?!!

This is the working code:
Private Sub Aanpassen_Click()
x = Application.Match(ComboBox1.Text, Range("A:A"), 0)
ActiveSheet.Range("B:B").Cells(x) = TextBox1.Value
MsgBox "Uurloon is aangepast!", vbInform, "Microsoft Excel"
Unload frmUurlonen
End Sub

Thanks to all,
Greetings from Belgium!