Multiple Match

[gregork]

Hi
I use the following code for a user form to edit a record from a list on a
sheet:

Dim anotherrng As Range, anotherres As Variant, anotherrng1 As Range
Set rng = Worksheets("Blending Details").Range("c2:c6000")

res = Application.Match(CStr(ComboBox1.Text), rng, 0)

If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, 3).Value = TextBox2.Text

The problem I have is the text in ComboBox1 can be listed more the once in
the sheet "Blending Details". So what happens is I end up editing the wrong
occurrence of the record. What I think I need to do is a double match to
ensure I am editing the right record. The second match is:

rng = Worksheets("Blending Details").Range("a2:a6000")
res = Application.Match.Sheets("Blend Sheet").Range("O3").Text

So in summary I need to match combo box1.text in Worksheets("Blending
Details").Range("c2:c6000")
providing the number in ("Blending Details").Range("a2:a6000") matches cell
O3 in sheet "Blend Sheet".

Can anyone advise on how to do this before I shove it in the too hard
basket.

Regards
gregorK

 

[Frank Kabel]

Hi
as a workaround you may try the following:
- add a helper column in your worksheet that concatenates column A and
C. e.g. column D with the formula
=A1 & C1
- copy this for all rows
- use this helper column for your MATCH function. e.g.

Dim anotherrng As Range, anotherres As Variant, anotherrng1 As Range
Dim lookup_value
lookup_value = Sheets("Blend Sheet").Range("O3").Text & _
CStr(ComboBox1.Text)
Set rng = Worksheets("Blending Details").Range("D26000")

res = Application.Match(lookup_value, rng, 0)

If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, 3).Value = TextBox2.Text

 

[gregork]

Hi Frank,
Thanks for your reply your suggestion works perfectly...I never thought
about trying that approach......brilliant...many thanks.

Kind Regards
gregorK