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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,247
Office Version
365
Platform
Windows
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:

henrik_ros

New Member
Joined
Jun 20, 2019
Messages
7
Hi

Thanks for reply :)

I got error when I type in textbox1

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


Regards Henrik
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,247
Office Version
365
Platform
Windows
Yes I spotted that (my typo!) and amended the code above
- use amended code :)
 
Last edited:

henrik_ros

New Member
Joined
Jun 20, 2019
Messages
7
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

New Member
Joined
Jun 20, 2019
Messages
7
Hi

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,247
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,089,165
Messages
5,406,555
Members
403,095
Latest member
cyclingdad

This Week's Hot Topics

Top