index and matching in userform!

[via135]

hi all!

i am having a userform with lables name, location..
but i am having a list of locations and the corresponding
postal codes in a separate worksheet

is it possible while i am entering the postal code in the location
textbox, the
corresponding location would be picked up in the main data worksheet?

regds!

via135

 

[Bob Phillips]

txtLOcation.Text =
Application.Index(Range("B:B"),Application.Match(txtPostcode.Text,Range("C:C
"),0))

where column B is the location column, C is the postcode.

You might want to qualify the ranges with the worksheet object

Worksheets("Sheet1").Range(...

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[via135]

hi Bob!

thks for the reply..
i am getting #NA error..

can u explain me the following a bit more?

You might want to qualify the ranges with the worksheet object
Worksheets("Sheet1").Range(...

thks n regds!

-via135

 

[Dave Peterson]

Are you sure that there's a match?

I'd do something like:

Dim res as variant
dim myVal as variant

res = application.match(txtpostcode.text,worksheets("sheet99").range("c:c"),0)

if iserror(res) then
myval = "No Match Found"
else
myval = worksheets("sheet99").range("b:B").cells(res,1).value
end if

txtLocation.text = myval

======
And by specifying the worksheet, you'll make sure that the code is looking at
the worksheet you want.

===
And if the data in column C is numeric (not Text), then maybe:

res = application.match(clng(txtpostcode.text), _
worksheets("sheet99").range("c:c"),0)

The value from the textbox will be text. clng() will convert it to a whole
number (if it's numeric).

 

[via135]

thks Dave..!

the clng() does the trick and
now the code works like charm..!

thks & regds!

-via135