index and matching in userform!
 

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

index and matching in userform!
 

txtLOcation.Text =
Application.Index(Range("B:B"),Application.Match(t xtPostcode.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" wrote in message
oups.com...
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

index and matching in userform!
 

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

On Jan 28, 3:51 pm, "Bob Phillips" wrote:
txtLOcation.Text =
Application.Index(Range("B:B"),Application.Match(t xtPostcode.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" wrote in ooglegroups.com...



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- Hide quoted text -- Show quoted text -

index and matching in userform!
 

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("she et99").range("c:c"),0)

if iserror(res) then
myval = "No Match Found"
else
myval = worksheets("sheet99").range("b:B").cells(res,1).va lue
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 wrote:

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

On Jan 28, 3:51 pm, "Bob Phillips" wrote:
txtLOcation.Text =
Application.Index(Range("B:B"),Application.Match(t xtPostcode.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" wrote in ooglegroups.com...



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- Hide quoted text -- Show quoted text -

--

Dave Peterson

index and matching in userform!
 

thks Dave..!

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

thks & regds!

-via135



On Jan 30, 1:20 am, Dave Peterson wrote:
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("she et99").range("c:c"),0)

if iserror(res) then
myval = "No Match Found"
else
myval = worksheets("sheet99").range("b:B").cells(res,1).va lue
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 wrote:

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

On Jan 28, 3:51 pm, "Bob Phillips" wrote:
txtLOcation.Text =
Application.Index(Range("B:B"),Application.Match(t xtPostcode.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" wrote in ooglegroups.com...

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- Hide quoted text -- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -