Application.Match

[henrik_ros]

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

 

[Yongle]

Welcme to the forum
You could try something like this
(tests for validity of data omittted to make example simple)

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

 

[henrik_ros]

Hi

Thanks for reply

I got error when I type in textbox1

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


Regards Henrik

 

[Yongle]

Yes I spotted that (my typo!) and amended the code above
- use amended code

 

[henrik_ros]

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

 

[henrik_ros]

Hi

variable res says Error = 2042 when I debug

/Regards Henrik

 

[henrik_ros]

Hi

I put your code on a button instead of a textbox_change - then it works

 

[Norie]

Henrik

You need to check if Application.Match has returned an error, which it will do if there's no match.

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

 

[Yongle]

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

 

[henrik_ros]

I will try

Thanks for all you help!

Regards Henrik