Application.Match

henrik_ros

New Member
Joined
Jun 20, 2019
Messages
7
Hello

I am pretty new to VBA and I have a question

In column A i have thousands of IDs, in column B their prices

I have a userform where the user enters ID in textbox1 and in textbox2 the prices is retrieved.

My question is if there is possible to enter multiple IDs in textbox1 and have those IDs added together in textbox2?
If the user enters IDs like ID1,ID2,ID3 in textbox1 - in textbox2 it would sum the prices together.

Below is the code, that works for 1 ID


Private Sub TextBox1_Change()


Dim Res As Variant

Res = Application.Match(TextBox1.Value, Worksheets("Blad2").Range("A1:A100"), 0)


If Not IsError(Res) Then
TextBox2.Value = Worksheets("Blad2").Range("B" & Res).Value
Else
TextBox2.Value = "Price is missing"
End If
End Sub



Best regards Henrik
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcme to the forum
You could try something like this
(tests for validity of data omittted to make example simple)

Code:
Dim arr As Variant, a As Variant, total As Double, res As Variant

arr = Split(TextBox1.Value , ",")

For Each a In arr
    res = Application.Match(a, Worksheets("Blad2").Range("A1:A100"), 0)
    total = total + Worksheets("Blad2").Range("B" & res).Value
Next

TextBox2.Value = total
 
Last edited:
Upvote 0
Hi

Thanks for reply :)

I got error when I type in textbox1

"Incompatible types" on this line
arr = Split(TextBox1.Value, , ",")


Regards Henrik
 
Upvote 0
Yes I spotted that (my typo!) and amended the code above
- use amended code :)
 
Last edited:
Upvote 0
Hi again and thank you

Now I got the same error on line:
total = total + Worksheets("Blad2").Range("B" & res).Value

Best regards Henrik R
 
Upvote 0
Henrik

You need to check if Application.Match has returned an error, which it will do if there's no match.
Code:
Dim arr As Variant, a As Variant, total As Double, res As Variant

arr = Split(TextBox1.Value , ",")

For Each a In arr
    res = Application.Match(a, Worksheets("Blad2").Range("A1:A100"), 0)
    If Not IsError(res) Then
        total = total + Worksheets("Blad2").Range("B" & res).Value
    End If
Next

TextBox2.Value = total
 
Upvote 0
The code is correct but will throw an error if res is an error OR if value in Range("B" & res) is not a number

Add tests in the VBA (like in your original code) to check both before adding the value to Total
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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