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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,283
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
4,283
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,288
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
4,283
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,078,437
Messages
5,340,278
Members
399,362
Latest member
iayb

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top